Trigger events can be fired within another trigger action. One Trigger execution can trigger even on another table or same table. This trigger is called NESTED TRIGGER or RECURSIVE TRIGGER. Nested triggers SQL Server supports the nesting of triggers up to a maximum of 32 levels. Nesting means that when a trigger is fired, it will also cause another trigger to be fired. If a trigger creates an infinitive loop, the nesting level of 32 will be exceeded and the trigger will cancel with an error message. Recursive triggers When a trigger fires and performs a statement that will cause the same trigger to fire, recursion will occur.
Disable Nesting/Recursing Triggers
Following script will stop executing all the nested triggers.
sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO
There is also alternate way to stop Trigger Recursion
ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF
Restrict Trigger Nesting to certain level
Put following script in trigger code. This will stop the trigger recursion after certain levels. In following case it will stop after 5 recursion.
IF ((
SELECT TRIGGER_NESTLEVEL()) > 5 )
RETURN
Reference : Pinal Dave (https://blog.sqlauthority.com)
28 Comments. Leave new
HOw to use trriger in sql server?
please as quick as possible
What is Trriger explined?
What is Indexes explined?
Hello,
I just wanted to thank you for the article, it put me out of big troubles with a trigger I was creating.
Keep helping people like that!!!!!!!! ;)
Hello ,
I have a INSERT/DELETE/UPDATE trigger on my master table which stores information about all the users.
When I am updating this master table with a query like Below
Update tblUserGeneral set Loc=2 where LearnerId in (’12’,’34’,’56’,’89’)
The trigger gets fired for the last Learnerid i.e. 89 but not for all the learnerids in where condition.
Why this happens? any solutions?
Let me know if more information is requuired from my side.
Even when many rows are updated, it’s a single update statement. That’s why the trigger is executed just once. The INSERTED and DELETED will contain all the rows modified during the update statement.
From what I understand an after trigger will only fire “after” a query completes successfully this means after all rows are effected, you need to take this into account when writing a trigger that deals with multiple row changes. you can always conditionalize on the @@ROWCOUNT variable if you need to have different trigger functionality defendant on whether only one row or multiple rows where effected. the inserted and deleted tables hold the data for all the rows effected not just the last.
Dave, excellent information as usual.
I want to execute two triggers simultaneously from one table. Both triggers do different things and need to work independently of each other i.e the first trigger does not need to fire the second trigger (no nesting involved). On a table insert, both triggers should fire.
Is this possible?
I am trying to nest stored procedures within SQLServer 2005, and I am getting the following error:
An INSERT EXEC statement cannot be nested.
I build a table and return it in one stored procedure; which is called by another stored procedure and stored in a temp table (see below).
INSERT INTO #EndPolicies EXEC [dbo].[stpGetExposure] @SnapshotTable,@StartDate,@PolicyType,@Company,@Territory
Where stpGetExposure returns a table.
Any help would greatly be appreciated.
Thanks
Joe Castelli
and stored in a temp table
I learnt the hard was (unconfirmed, but it happened to me) that the recursive_triggers option is off by default.
Watch out for that one!
rts
Thats right.
Recursive_Triggers option is set to OFF by default.
Nested Triggers option is set to ON by default.
With the above settings nested triggers will work but not recursive ones.
Hello sir.
I m Using trigger for reflect changes of Insert,Updateand Delete
For Insert i Using
if exists(select * from inserted)
Begin
/insert into another table
End
for delete i using
if exists(select * from deleted)
Begin
/insert into another table
End
and for update
if exists(select * from inserted) and exists(select * from inserted)
Begin
End
but at the time of Updation Three Operations Of Delete,Insert and Update are Fired i Want only Update row to Add in Another table
How to do that Pls. Help.
Use
If update(col)
in for update option
I try That also but than also arising same problem in it
What type of TRIGGER are you using?
You should be able to specify AFTER INSERT, then another TRIGGER as AFTER DELETE, and so on.
/*IN THIS EXAMPLE WE ARE USING ALL OPTION IN ONE TRIGGER, YOU NEED TO DECLARE ALL VAR. FOR ALL COLUMNS OF MASTER TABLE FOR INTIALIZING RECORDS FROM INSERTED/DELETED VIRTUAL TABLES AND MUST ISSUE A COMMAND FOR UPDATE/INSERT/DELETE FOR COMMIT IT TO MASTER TABLE ELSE IT WILL REFLECT ONLY IN BACKUP FILE I.E. EMP_BK BECAUSE WE ARE USING INSTEAD OF TRIGGER FOR WHICH WE NEED TO PASS ANOTHER CONFIRMATION COMMANDS INSIDE THE TRIGGER*/
————–
Alter trigger trg_insteadof_emp on dbo.emp
instead of INSERT,DELETE,UPDATE —USING ALL OPTIONS AT ONE GO
as
begin
declare @eno int
declare @enm varchar(10)
declare @sal int
IF EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
BEGIN
select @eno=I.empno from INSERTED I
select @enm=I.ename from INSERTED I
select @sal=I.sal from INSERTED I
insert into emp_bk select @eno,@enm,@sal,’Record Updated’,getdate()
print ‘Record Updated in Master Table – instead of update trigger raised’
END
ELSE IF EXISTS(SELECT * FROM INSERTED)
BEGIN
select @eno=I.empno from INSERTED I
select @enm=I.ename from INSERTED I
select @sal=I.sal from INSERTED I
insert into emp_bk select @eno,@enm,@sal,’New Record Inserted’,getdate()
print ‘Record Inserted to Master Table – instead of Insert trigger raised’
END
ELSE
BEGIN
select @eno=d.empno from deleted d
select @enm=d.ename from deleted d
select @sal=d.sal from deleted d
insert into emp_bk select @eno,@enm,@sal,’Record Deleted’,getdate()
print ‘Record Deleted from Master Table – instead of delete trigger raised’
end
end
————————
-MALKEET SINGH
Thank you Sir very much!
dear sir,
i want to insert, update,and delete from two table using triggers ONE TABLE MASTER TABLE,AND ONE TRANSACTION TABLE,IN MASTE TABLE A/CNO = ‘A001’,BALANCE = 20000,
IN TRANSACTION TABLE A/C NO = ‘A001’,A/CTYPE = WITHDRAWL/DEPOSIT,AMOUNT= 200
I HAVE WITHDRAWL 200 FROM TRANSACTION TABLE AND IT SHOULD BE WOTHDRAWL FROM MASTER TABLE AND BE DELETED 200 FROM MASTER HOW CAN I DO IT IN TRIGGERS
AND IF EXIST THE AC TYPE ……..
HOW CAN IT POSSIBLE ?????
You are the GURU of SQL,
Great work…..
Thanks
How to fire insert trigger foreach row
when using
insert into ()
select from
i want to create trigger if i update one column value. it should update another Table column’s value in same DB
Dear Pinal sir,
Its a great SQL Tip for me.
Thanks & Regards,
Nikhildas
Vishnu
Dave Can u please explain Trigger
while running trigger its other related transaction will be locked?
I have trigger for one column update from Stored procedyre. when updated its ruunning. my doub is, if trigger takes more time means . whether other remaining query will be locked?
Please clarify … pls help me
Hi Pinal Dave,
When we create “Instead of delete” trigger on particular table , after creating of cursor want to delete rows from table and in trigger some condition exists.
if given condition is satisfied by compiler then cursor rollback deleted records to table and condition is not satisfied then cursor commit that record.
But Here I have question that “Instead of delete ” cursor set automatically “begin transaction” while cursor invoked or we have to set “begin transaction” before invoking the trigger action?
Thanks in advance…
and awaiting for your earliest reply…..