You must Sign In to post a response.
  • Category: SQL Server

    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
    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.

    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)

  • #764537
    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.
    Shashikant Gurav

Sign In to post your comments