Recursion in trigger

Category: SQL Server
29 Jan 2016 03:26
Joined: 05/02/2011
Level: Silver
Points: 102
Points: 5
Recursion in trigger
Table1 have a trigger,when I am inserting record in Table1,trigger get fired which insert record into Table2.Now I want to do change in it,when I insert record in Table1,trigger get fired which insert record into Table2 and if para1 =1, again I want record in Table1.So I executed sp in trigger which insert record into Table1.Sp is able to insert record in Table1 from trigger,but second time trigger does not get tired unable to insert record in table2.If I execute same sp outside trigger,able to insert record in table1 and trigger get fired which insert record into table2.How to handle such senario
#764342
31 Jan 2016 07:21
Joined: 28/04/2006
Level: Gold
Points: 29959

Points: 3
Hai Pinky,
In the second case, the trigger will not get fired because of the condition checking. Trigger gets fired based on the database objects like table and not based on the conditions.
So you need to do something explicitly to insert the records to the table2.
Hope it will be helpful to you.
Regards,
Pawan Awasthi(DNS MVM)
+91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
pawansoftit@gmail.com
#764537
11 Feb 2016 02:38
Joined: 15/12/2015
Level: Gold
Points: 1198
Points: 4
Hi pinky,
1) If para1=1 then you are inserting record to Table1 through SP. which interns calls trigger placed on Table1 to insert record in table2.
Here if you want record from Table1 again then you can do that with the help of query as you are executing SP programmatically and set the parameter para1 again back to 1 so it will insert record in Table1 and so on.
2) Same SP when you are executing on sql server it will always take para1 = 1, so it is executing properly.
Hope it helps.
Regards,
Shashikant Gurav
shashikantgurav22@gmail.com

Joined:	05/02/2011
Level:	Silver
Points:	102

Joined:	28/04/2006
Level:	Gold
Points:	29959

Joined:	15/12/2015
Level:	Gold
Points:	1198

Recursion in trigger

Email subscription