May 22, 2009 at 10:51 am
Hi
We have just switched from Sql 2005 to Sql 2008 and our nested triggers are not working. Im not sure if it was this way before or if they were working before. Here is the trigger:
ALTER TRIGGER [dbo].[td_trend_s] ON [dbo].[trend_s]
FOR DELETE
AS
DECLARE @PKey int
SELECT @PKey = P_key from deleted
delete from trend_u where P_key = @PKey
delete from trend_t where P_key = @PKey
delete from trend_d where P_key = @PKey
delete from trend_s_p where P_key = @PKey
So far so good, it deletes all values from the other tables. But, the trend_d also has a trigger to delete from another table:
ALTER TRIGGER [dbo].[td_trend_d] ON [dbo].[trend_d]
FOR DELETE
AS
DECLARE @PDeKey int
SELECT @PDeKey = trend_dkey from deleted
delete from trend_d_i where trended_dkey = @PDeKey
This trigger does not fire.
So when the first trigger calls "delete from trend_d where P_key = @PKey" it should fire the second delete trigger from that table which calls
"delete from trend_d_i where trended_dkey = @PDeKey"
But the second trigger never happens.
Help Please
Thx
Pete
May 22, 2009 at 12:51 pm
Have you checked BOL to determine the setting for Nested Triggers?
http://msdn.microsoft.com/en-us/library/ms191520.aspx It could have been set by someone so as to not allow nested triggers ... if it is set to allow nested triggers please post and someone can attempt to assist you further.
May 22, 2009 at 2:08 pm
I'd also be concerned that this trigger, and I would assume the others, only handles a single row delete. What happens if there is a set-based delete?
I'd code the trigger this way:
ALTER TRIGGER [dbo].[td_trend_s] ON [dbo].[trend_s]
FOR DELETE
AS
SET NOCOUNT ON
delete from
trend_u
where
P_Key IN (SELECT P_Key FROM deleted)
delete from
trend_t
where
P_key IN (SELECT P_Key FROM deleted)
delete from
trend_d
where
P_key IN (SELECT P_Key FROM deleted)
delete from
trend_s_p
where
P_key = IN (SELECT P_Key FROM deleted)
ALTER TRIGGER [dbo].[td_trend_d] ON [dbo].[trend_d]
FOR DELETE
AS
DECLARE @PDeKey int
delete from
trend_d_i
where
trended_dkey IN (SELECT trend_dkey FROM deleted)
If you are doing the delete via stored procedure why not do all the deletes in the stored procedure (possibly using child procedures for each table).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply