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?

more ▼

asked Nov 13, 2009 at 09:27 PM in Default

bdavid gravatar image

1 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Nov 14, 2009 at 12:18 AM

TG gravatar image

1.8k 1 3

(comments are locked)
10|1200 characters needed characters left

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

IF @intErrorNo = 0
COMMIT TRANSACTION MyTransactionName_01 -- Outer transaction
ROLLBACK TRANSACTION MyTransactionName_02 -- Inner transaction
COMMIT TRANSACTION MyTransactionName_01 -- Outer transaction
more ▼

answered Nov 14, 2009 at 04:05 PM

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(comments are locked)
10|1200 characters needed characters left

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?

more ▼

answered Nov 16, 2009 at 03:54 PM

user-454 (yahoo) gravatar image

user-454 (yahoo)
23 4 5 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 13, 2009 at 09:27 PM

Seen: 1538 times

Last Updated: Nov 14, 2009 at 04:06 PM