Post reply

AFTER DELETE Trigger doesn't fire on Cascaded Delete

  • 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?

  • September 30, 2008 at 4:58 am

    #878753

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

    Run sp_configure and see if this is set to 1.

  • September 30, 2008 at 5:04 am

    #878756

    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?

  • September 30, 2008 at 5:53 am

    #878774

    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.

  • September 30, 2008 at 9:03 am

    #878917

    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?

  • September 30, 2008 at 11:03 am

    #879011

    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]

  • October 7, 2008 at 5:09 am

    #881546

    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

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

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