AFTER DELETE Trigger doesn't fire on Cascaded Delete

Question

Post reply

AFTER DELETE Trigger doesn't fire on Cascaded Delete

Rob Levy

Mr or Mrs. 500

Points: 565
More actions
September 30, 2008 at 3:57 am

#193761

Hi all,
I've had a good read around and can't find any reference to this behaviour; any thoughts?
Trigger works with direct delete
I have an AFTER DELETE trigger on table B.
Deleting from table B INSERTs a row into table C saying what was deleted from B and when. Very useful!
Rows are deleted through a foreign key constraint
A-->B
B has a foreign key constraint to a PK in A and the DELETE behaviour is set to CASCADE, meaning that deleting a row in A can cause rows in B to be deleted too.
Delete trigger doesn't fire
From my tests, my AFTER DELETE trigger on table B doesn't fire when I delete rows from table A, even though rows are indeed deleted from B.
I'd obviously like the trigger to fire in both the direct delete and the cascade delete scenario. Is this possible?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

Do you have the server property "nested triggers" set?

Run sp_configure and see if this is set to 1.

Rob Levy Mr or Mrs. 500 Points: 565 More actions · Answer 2

Hi Ian,

thanks a lot for the super-fast response!

Nested Triggers:

Min: 0

Max: 1

config_value: 1

run_value: 1

I assume this means it's enabled?

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 3

A couple more thoughts...

Are you sure the trigger doesn't fire?

Just add a print statement to the top of the trigger to confirm whether or not it fires.

Does your Table B trigger join to Table A perhaps, and those rows aren't there if the delete started at Table A.

Can you post the trigger code if you still have problems.

Rob Levy Mr or Mrs. 500 Points: 565 More actions · Answer 4

Hi again,

you're absolutely right, well spotted.

The trigger is running, but not doing what I expected.

Inside the trigger I'm trying to join to something which has already been deleted, so the join returns no rows, so the log table remains empty.

So I need to restate my question:

A---->B

How can I log the state of both A and B before the delete using a trigger on either A or B?

I need a "Before Delete" type trigger, but it seems they don't exist. Do I have any sensible alternatives?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

You could use INSTEAD OF triggers on B, but I think that you will still have problems.

What you really need is a combined trigger from A & B, but of course nothing like that exists.

You could put you trigger on A instead, and then just assume that the cascade-delete will catch the corresponding rows in B. Of course it would be more reliable then to remove the cascade and just have the Delete trigger on A do it.

The real problem here is that you are trying to use physical-level tools like FK-cascades and Table Triggers to do Logical management of "Entities". What really should be done is to re-design the DB & application to use Stored Procedures to implement the this kind of logical level entity management.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Rob Levy Mr or Mrs. 500 Points: 565 More actions · Answer 6

Thanks all for your responses.

My final decision was just not to be lazy.

I've put separate triggers on each table, logging to separate "Deleted" tables, and can now do the join I need on the "Deleted" tables, rather than get everything into one table.

Thanks for all your help.

Rob