Post reply

In Cascade Delete how to get values from master for "after delete trigger" on child

  • June 26, 2006 at 11:56 am

    #113554

    Here is my Problem

    (1) There is a "master" table and "child" table. We have a CASCADE DELETE on for the "child" table.

    (2) There is a "after delete" trigger on "child" table that tries to do a join with the "master" table.

    (3) When a record is delete from "master" table it cascades to child and then when the "after delete" trigger is fired it joins with the "master" table but because the data has already been deleted from "master" table for that entity_id it provides no rows for that join.

                 SOMTHING LIKE FOLLOWING INSIDE AFTER-DELETE TRIGGER on the "CHILD" TABLE

                              DECLARE @somevalue INT

                              SELECT @somevalue = master.somevalue

                              FROM deleted

                              INNER JOIN master ON deleted.entity_id = master.entity_id

     

    IS THERE ANY WAY I CAN GET THE VALUES FOR THE "MASTER" TABLE ROW THAT HAS JUST BEEN DELETED AND GETTING CASCADED TO THE CHILD?

    Thanks for the help

  • June 26, 2006 at 12:46 pm

    #645903

    You could use an 'instead of delete' trigger on the parent table to join to the child records and do whatever it is you want to do in the trigger from there, unless the parent table also has a FK with a cascade delete setting.

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • June 26, 2006 at 12:56 pm

    #645904

    Thanks Tim

    I was wondering if there is a way where by putting any LOCK hint etc I can still read that deleted (but uncommited row) from the master table.

  • June 26, 2006 at 4:33 pm

    #645942

    No sensible or reliable way of doing that. Once any triggers on the parent table have run, SQL won't let you intervene until any deletes have been cascaded. That's why they don't allow 'instead of' triggers on columns subject to cascade operations. This is because the cascade is required for referential integrity so mustn't be overridable. You can do anything you need to from the trigger on the parent table, though. It may also not be necessary to use a trigger - what does it do?

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

Viewing 4 posts - 1 through 3 (of 3 total)

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