SQL Server 2008 "BEFORE DELETE" trigger

[JScannell]

Apparently SQL Server took away the "BEFORE DELETE" trigger and replaced it with an "INSTEAD OF".

I also have an "AFTER DELETE" that writes out a log record that now doesn't fire because of the "INSTEAD OF" I had to add! As a result, the original record I wish to delete is still there.


So, how do you go back and do the "AFTER DELETE" trigger when the "INSTEAD OF" is done?

(the reason for the "INSTEAD OF" is I have to delete records that have FK columns pointing to the record I wish to delete, so I have to remove them first)

Thanks in advance,

Jerry Scannell

 

[gmmastros]

I encourage you to do a little research on cascade delete. From the sounds of it, it's exactly what you are looking for and doesn't involve any triggers.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

I'm not in favor of cascade delete, but why not do all the deleting in the instead of trigger? Or are you using the instead of trigger properly? You know that it takes the place of the actual delete, so if you still want to do the delete it has to be specified in the instead of trigger.

And I never heard of SQL Server having a BEFORE DELETE trigger, at least as far back as SQL Server 2000 it has only had an instead of trigger and after triggers.

"NOTHING is more important in a database than integrity." ESquared

 

[gmmastros]

Just curious Sis, why are you not in favor of cascade delete?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JScannell]

It turns out that SQLSister is right. I finally got it to work by taking care of my child deletes first, and then re-issuing the original delete inside the "INSTEAD OF" trigger and it works.

Thanks to everyone for your comments...


Jerry Scannell

 

[SQLSister]

Cascade delete can cause problems if many records are deleted. You can end up with all the child and parent tables locked and no access for anyone else until the delete finishes. We can have a delete from the parent table that could cause thousands or even millions of subordinate records to delete, so we don't allow cascade delete. Plus I prefer to have things stop if I have child records. Could be that means I shouldn't be delting the parent record to begin with.

"NOTHING is more important in a database than integrity." ESquared

 

[markros]

I think Nikola gave similar reasoning, but I don't recall the exact thread where he did.

PluralSight Learning Library