August 5, 2009 at 8:59 am
Hi,
I have an update statement inside an update trigger that updates the 'date_modified' in the same table.
Is there a setting (like in sys.configurations) that stops Sql Server from recursively firing another UPDATE TRIGGER and go on... until it hits the 'nested/recursive trigger limit'
SELECT * FROM sys.configurations
-- RESULT (reformatted)
115 nested triggers 1 (Allow triggers to be invoked within triggers)
Should I use 'INSTEAD OF' TRIGGER rather than AFTER trigger? I read that 'INSTEAD OF' trigger cause less overhead if there is a need for ROLLBACK.
thanks for your input,
_UB
August 5, 2009 at 9:10 am
you could do several things here
1st - disallow recursive triggers using
sp_configure 'nested triggers',0
but this might break other DB functionality
possibly i might suggest performing a check on the columns updated.
CREATE TRIGGER xxxx on xxx FOR UPDATE
AS
IF UPDATE column --(OR you can use IF (COLUMNS_UPDATED() )=...... )
BEGIN
.....
....
END
I would advise against doing too much in triggers as they will slow you down big style
MVDBA
August 5, 2009 at 9:49 am
Thanks for the response.
No matter which column is updated, 'Date_Modified' needs to be updated. So, looks like INSTEAD OF Triggers might be a easier route.
thanks,
_UB
August 5, 2009 at 12:11 pm
This setting is :
SELECT name, Is_Recursive_Triggers_On FROM sys.databases
Looks like the default is OFF
August 5, 2009 at 3:20 pm
UB (8/5/2009)
Thanks for the response.No matter which column is updated, 'Date_Modified' needs to be updated. So, looks like INSTEAD OF Triggers might be a easier route.
thanks,
_UB
considering it will look like a single operation and should NOT trigger the recursive trigger (if it does you're doing it wrong), I'd agree - INSTEAD OF is the correct answer.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 7, 2009 at 10:42 am
Matt,
I agree with the INSTEAD OF TRIGGERS, that's what I am gonna write my triggers in. Not all, but only some TRIGGER that do more than just auditing.
From what I read, if '[font="Courier New"]Is_Recursive_Triggers_On[/font]' is ON, performing an UPDATE within an UPDATE TRIGGER will fire itself. This continues until the limit 32 is reached.
Since its set to OFF in my database, it doesn't fire itself recursively.
thanks for the feedback,
_UB
August 7, 2009 at 12:01 pm
UB (8/7/2009)
Matt,I agree with the INSTEAD OF TRIGGERS, that's what I am gonna write my triggers in. Not all, but only some TRIGGER that do more than just auditing.
From what I read, if '[font="Courier New"]Is_Recursive_Triggers_On[/font]' is ON, performing an UPDATE within an UPDATE TRIGGER will fire itself. This continues until the limit 32 is reached.
Since its set to OFF in my database, it doesn't fire itself recursively.
thanks for the feedback,
_UB
If you reissue the update in the INSTEAD OF, based on the INSERTED table, it should NOT trigger it. That said - there are fairly few cases I have run into where the Recursive triggers would be a good idea it's much better to leave that off.
from BOL:
If an INSTEAD OF trigger defined on a table executes a statement against the table that would ordinarily fire the INSTEAD OF trigger again, the trigger is not called recursively. Instead, the statement is processed as if the table had no INSTEAD OF trigger and starts the chain of constraint operations and AFTER trigger executions. For example, if a trigger is defined as an INSTEAD OF INSERT trigger for a table, and the trigger executes an INSERT statement on the same table, the INSERT statement executed by the INSTEAD OF trigger does not call the trigger again. The INSERT executed by the trigger starts the process of performing constraint actions and firing any AFTER INSERT triggers defined for the table.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 7, 2009 at 2:10 pm
Yes. I agree. I read the same for INSTEAD OF triggers.
My earlier comment was based on what I read on AFTER triggers. I guess my comment did not convey that point across.
thanks for your feedback,
_UB
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply