Help with trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with trigger

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-06-17 : 16:49:03

Hi,

Table1
=======
id1 int fk
id2 int fk
..

Table2
======
id1 int pk
id2 int pk
...

(These tables have other columns but they're not relevant here)

Basically, rows in Table1 don't have to reference a row in Table2 but they can.

However, a row cannot exist in Table2 if its not being referenced by at least one row in Table1.

So I want to put a delete trigger on Table1 such that when you delete a row in Table1, it checks to see if this is the last row in Table1 referencing the currently referenced row in Table2 and, if so, to delete the row in Table2.

How do I implement this?

One idea is to select all rows in Table1 with the fk of the row to be deleted from Table1 and, if the result set is empty, then to delete the referenced row from Table2. However, what happens if just after an empty result set has been returned a new row in Table1 is added which means the row in Table2 can actually live on?

Also, I don't understand how to actually select all the rows in Table1 with the curren fk. In a trigger you have a delete table filled with all the rows to be deleted. How do you process each row to be deleted in the way I have just mentioned without using a cursor?

Cheers,

XFactor.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-17 : 18:26:50

Build a procedure that does the deletes from these tables. Never delete directly from other procs. If you do this, you can just consolidate the entire process in one procedure.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-06-17 : 18:32:35

Thanks for your reply.

I'm not sure how that answers the question though.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-17 : 18:40:43

To accomplish what you are describing with a trigger, you can join the deleted back to Table2, then to Table1 with a left join where the join is null

DELETE t2
FROM
deleted d
INNER JOIN Table2 t2 ON d.id = t2.id
LEFT OUTER JOIN Table1 t1 ON t2.id = t1.id
WHERE
t1.id IS NULL

Transactional integrity will insure the process is consistent referentially. You just need to make sure you don't have any stored procedures verifying rows in Table2 using WITH(NOLOCK) to see if rows exist. This could mess up your whole world. :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-17 : 18:41:38

Perhaps if you posted DDL, DML for sample data and expected result set, then we'd be able to provide a solution.

Tara

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-06-17 : 19:22:22

That seems quite cool derrickleggett. Thanks.

So I join deleted to Table1 and delete the rows and then do the join that you have suppied and both of theses deletes are within a transaction.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-17 : 19:29:50

Yes. Beware of NOLOCK. :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-06-17 : 22:53:36

quote:
Originally posted by derrickleggett

DELETE t2
FROM
deleted d
INNER JOIN Table2 t2 ON d.id = t2.id
LEFT OUTER JOIN Table1 t1 ON t2.id = t1.id
WHERE
t1.id IS NULL

Just curious, is this preferable (performance wise, I suppose) to a NOT EXISTS ?

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-17 : 23:47:04

If you have proper indexes I prefer it. Definitely better then not in.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-06-18 : 04:13:43

quote:
Beware of NOLOCK.

Is there anything special about this situation that makes concurrent use of NOLOCK a troublesome thing to do?

I'd have thought that if any query is using NOLOCK there's a risk of getting inconsistent data.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-06-18 : 04:20:30

quote:
Just curious, is this preferable (performance wise, I suppose) to a NOT EXISTS ?

Did you have something like this in mind...

DELETE t2
FROM
deleted d
INNER JOIN Table2 t2 ON d.id = t2.id
WHERE NOT EXISTS (SELECT * FROM Table1 t1 WHERE t1.id = t2.id)

I prefer the join myself. Mainly because the query feels more integrated. Does that mean anything to anyone?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-18 : 08:57:36

I again prefer the LEFT JOIN in this case. The NOLOCK can be troublesome because it reads through locks. It's alright if you're just reading data and are aware of this. If you're using that select to do an insert though, that can obviously cause problems. You just have to be aware of what it does and think through the issues.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-06-18 : 13:34:02

quote:
Originally posted by X-Factor

[quote]Did you have something like this in mind...

Yup.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources