June 26, 2006 at 11:56 am
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
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
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
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