I have a table1 and a "After Insert Trigger" called Trig1 on table1 in SQL Server. The Trig1 writes the record after some modification into Table2. When I try to insert a Record in Table1 if the logic within Trig1 to insert data into Table2 fails because of some reason(lack of permission, duplicate id etc) the record is not inserted into table 2 and neither is it inserted in table1. It seems that the initial insert and trigger together is considered as one transaction and is rolled back because of the failure to enter data in table2. But i would like the insert into table1 still to be committed whether the insert into table 2 took place or not. Is there a way to do it?
You are correct that the actions in a trigger are part of the same transaction as the action to the table (that has the trigger). That is by design and the way it should be. For that reason the trigger statement(s) should be simple, fast, and very unlikey to fail.
It is possible to put an explicit COMMIT transaction in the trigger but MS recommends against that. (seel "commit" transaction in books online)
If you have launch external processes or perform long running statements, or perform actions which have some risk of failure due to constraints, permissions etc, then you should isolate those actions from the user transaction. One way to do that is to do a simple insert into a processing table. Then use a job or service to monitor that processing table and perform the work.
answered Nov 14, 2009 at 12:18 AM
You could add some conditional statements in your Trigger so that in the event of attempting to create a Duplicate transaction your Trigger takes an appropriate action.
It would seem reasonable to me that if the actions in the Trigger create circumstances that you need to rollback (but which still allow the outer transaction to complete) you should do that.
In order to avoid errors saying that "the transaction count is incorrect" you may need to create a localised transaction that you ALWAYS commit, within which is a further nested transaction that you can optionally rollback:
BEGIN TRANSACTION MyTransactionName_01 SAVE TRANSACTION MyTransactionName_02
answered Nov 14, 2009 at 04:05 PM
I would have liked to insert it into a intermediate processing table and then do the processing from there seperately instead of using a trigger as suggested. But will that not cause a timelag? Is it possible to process the records through a SQL job or service in the same way as we do it using a trigger without much processing delay time. I guess it would depend then on the frequence in which the job runs right?
answered Nov 16, 2009 at 03:54 PM