question

bdavid avatar image
bdavid asked

Database Triggers and commit

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?

sql-server-2005trigger
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TG avatar image
TG answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kristen avatar image
Kristen answered

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            
            
... Code which sets @intErrorNo if there is a problem ...            
... You can put IF @intErrorNo  0 GOTO MyLabel_ABORT in the code, if you wish ...            
            
MyLabel_ABORT:            
IF @intErrorNo = 0            
BEGIN            
    COMMIT TRANSACTION MyTransactionName_01	-- Outer transaction            
END            
ELSE            
BEGIN            
    ROLLBACK TRANSACTION MyTransactionName_02	-- Inner transaction            
    COMMIT TRANSACTION MyTransactionName_01	-- Outer transaction            
END            
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

user-454 (yahoo) avatar image
user-454 (yahoo) answered

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?

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.