June 2, 2012 at 4:47 pm
I have a trigger that is firing with out issue in non-production but the same exact code is failing to fire in production. I've confirmed it's not disabled and I've done a trace to see that it is not even executing in production.. I've checked the relevant OBJECTPROPERTY elements and they are the same. I've confirm the code is the same. I've confirmed the same inserts are coming from the application. Below is the code for this trigger:
****** Object: Trigger [dbo].[tr_trigger_ins] Script Date: 06/02/2012 16:51:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE TRIGGER [dbo].[tr_trigger_ins] ON [dbo].[table_1]
FOR INSERT
AS
BEGIN
UPDATE table_2
SET col_1 =
CASE
WHEN i.col_2 = '0' THEN 0
ELSE 1
END
FROM INSERTED i
INNER JOIN table_3 pa
ON i.col_3 = pa.col_3
AND pa.col_4 = 'ispublic'
INNER JOIN table_4 pp
ON i.col_5 = pp.col_5
INNER JOIN table_2 cs
ON pp.col_6 = cs.col_6
END
GO
Below is a trigger that is an instead of insert on the same table that is executing in both environments:
****** Object: Trigger [dbo].[tr_trigger_before_ins] Script Date: 06/02/2012 16:55:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE TRIGGER [dbo].[tr_trigger_before_ins] ON [dbo].[table_1]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO table_1
SELECT * FROM INSERTED
WHERE col_3 in (73, 199)
END
GO
Any help is greatly appreciated.
Russ
June 2, 2012 at 5:16 pm
Out of curiousity, in your production (not working environment), does your INSERT statement have col3 set with a value of either 73 or 199? If it doesn't, then you AFTER INSERT trigger will not fire.
Also, what trace events are you capturing? Do the SP statements of your AFTER INSERT trigger just not appear at all in the trace, or do they appear but there is no INSERT into table_1?
June 2, 2012 at 6:07 pm
It is in 73 and it fires for the instead of trigger. Traced rpc completed, statement completed,batch completed.
Russ
June 2, 2012 at 7:14 pm
Which statement completed?? Try SP:StmtCompleted. I can't seem to recreate your problem in my personal environment. Your code works for me.
I'm thinking it may actually be firing (hopefully you're just capturing the wrong events) but your INNER JOINs are leaving you with no rows for the AFTER INSERT trigger. My guess (but probably just hopeful thinking). Also, your table_2 has data in it right?
June 2, 2012 at 7:19 pm
Well I can see the insert come in and I capture that and it meets the condition. As a matter of a fact I can run the a select based off the trigger query and it will show it is satisfied to do that I replace the inserted with the name of the table. I know the data meets the condition to fire but is not. My non prod it works every time and it's basically the same data except for an id number coming it tht is not compared in this query. That is the weird thing about this issue.
Russ
June 2, 2012 at 7:55 pm
Hmmm...that is really odd. I'd dumb down the AFTER INSERT trigger to do something as simple as an INSERT into a tempdb table just to see if it is happening.
I don't know how you feel about this, but if you'd like I could take a look at it through a join.me session. If this is production for your work, though, you'd probably not want to do this. But it may be easier to troubleshooting with looking at it. Your call. My email is in my signature if you decide you do.
June 2, 2012 at 8:04 pm
Here's another question for you. What security context is the 2nd trigger executing under? I'm guessing you do, but a check would be if you have permissions on the db objects that the 2nd trigger is manipulating and selecting from. It might heed you to put a WITH EXECUTE AS clause for the trigger to make sure the correct permissions are present. Just another guess...
June 2, 2012 at 9:41 pm
Are nested triggers disabled?
Select value_in_use
from sys.configurations
where name = 'nested triggers';
Instead of triggers are executed before an after trigger, and if nesting is disabled, the insert from the instead of trigger won't be fired.
June 2, 2012 at 9:47 pm
Running as dbo. I'm planning to open an SR Monday if I can't figure something this weekend. Given both are in the same security context I think that is OK. May do a trace with error capture just in case.
Russ
June 2, 2012 at 9:47 pm
Robert Davis (6/2/2012)
Are nested triggers disabled?Select value_in_use
from sys.configurations
where name = 'nested triggers';
Instead of triggers are executed before an after trigger, and if nesting is disabled, the insert from the instead of trigger won't be fired.
Isn't that only for cascading AFTER triggers? http://msdn.microsoft.com/en-us/library/ms178101.aspx
I think in this case with an INSTEAD OF and then AFTER it won't technically be a nested trigger. I have run a test and with nested triggers disabled it still fires my AFTER trigger.
June 2, 2012 at 10:36 pm
Yes, good point. Only affects after triggers, not instead of triggers.
June 3, 2012 at 4:03 am
I see in your example triggers all on table_1, you're only updating table table_2 in the first one.
For that one, I would use
...UPDATE cs
SET col_1 = ...
because we've had issues donig in your style, but maybe that's an old issue, fixed in SQL2008.
Keep in mind you can also set trigger recursivity at db level !!
http://msdn.microsoft.com/en-us/library/ms189799(v=sql.105).aspx
Recursive Triggers
SQL Server also allows for recursive invocation of triggers when the RECURSIVE_TRIGGERS setting is enabled using ALTER DATABASE.
Recursive triggers enable the following types of recursion to occur:
Indirect recursion
With indirect recursion, an application updates table T1. This fires trigger TR1, updating table T2. In this scenario, trigger T2 then fires and updates table T1.
Direct recursion
With direct recursion, the application updates table T1. This fires trigger TR1, updating table T1. Because table T1 was updated, trigger TR1 fires again, and so on.
select name, is_recursive_triggers_on
from sys.databases
/*where name = 'yourdb'*/
order by name;
Double check its setting !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 4, 2012 at 8:16 am
Confirmed they are not disabled. Thank you.
Russ
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply