Set recursive Trigger ON/OFF

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 226
There are some scenarios to deal with Recursive triggers.

For suppose, you have one trigger for all DML operations ( INSERT, UPDATE and DELETE) on table and the trigger logic is doing one update operation on same table with respect to each INSERT operation.

CREATE TRIGGER TriggerName ON tableA
FOR INSERT, UPDATE, DELETE
AS
BEGIN

if Operation is INSERT
--DO an UPDATE on same table
else if it is UPDATE
-- DO an INSERT on same table
else
-- DO an logging operation

END



Due to the one round of INSERT/UPDATE operation inside a trigger, it will do recursive triggering...

Now, if you really need the recursive ness of this trigger, you can enable by setting
RECURSIVE_TRIGGERS ON ; else you can prevent this behaviour by setting RECURSIVE_TRIGGERS OFF at database-level. By default, the RECURSIVE_TRIGGERS is OFF...

ALTER DATABASE DatabaseName 
SET RECURSIVE_TRIGGERS ON



ALTER DATABASE DatabaseName 
SET RECURSIVE_TRIGGERS OFF

Comments or Responses

Login to post response