Working TRIGGER and TRANSACTIONS QUESTION

5 pts.
Tags:
SQL Server 2005
SQL Trigger
Transactions Per Second
TRIGGER
Hello all,

My company purchased an operations software (called [strong]darwin[/strong]) that works with SQL 2005 Server. I need to extract some data and save it in my Own Database, which is located in the same SQL Server. On the darwin software there is a screen that updates a field X in a table, which fires an an AFTER UPDATE Trigger that I created  and saves some darwin's data into my own db.

So far so good. Now, my trigger uses Transactions and under some situations, the trigger rollback changes, but that seems to be afecting darwin's software, and the rollback is affecting the UPDATE they have in the screen I already comment and the field X is not updating(The provider said they are not using transactions) . I thought that transaction inside a trigger will not affect any sentences but the ones inside the trigger. Can anyone explain me why is this happening? Is there a way to overcome this?

Thanks in advance

Luis Garcia Software Components

ASKED: September 30, 2009  6:25 PM
UPDATED: October 1, 2009  9:07 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

First, everything is in some kind of “transaction”. When you do a “Begin Transaction”, you are stating that everything up to the next “Commit” (or Rollback) is to be considered as a single transaction. If you execute a SQL statement when there is no “Begin Transaction” active, that single statement is itself a transaction.

From the MSDN section on “Commits and Rollbacks in Stored Procedures and Triggers” (my emphasis added in places):

————————————————–

<b>A trigger operates as if there were an outstanding transaction in effect when the trigger is executed. This is true whether the statement firing the trigger is in an implicit or explicit transaction.</b>

When a statement begins executing in autocommit mode, there is an implied BEGIN TRANSACTION to allow the recovery of all modifications generated by the statement if it encounters an error. This implied transaction has no effect on the other statements in the batch because it is either committed or rolled back when the statement completes. This implied transaction is still in effect, however, when a trigger is called.

When a trigger executes, an implicit transaction is started. If the trigger completes execution and @@TRANCOUNT = 0, error 3609 occurs and the batch is terminated. If a BEGIN TRANSACTION statement is issued in a trigger, it creates a nested transaction. In this situation, when a COMMIT TRANSACTION statement is executed, the statement will apply only to the nested transaction.

When using ROLLBACK TRANSACTION in a trigger, be aware of the following behavior:

All data modifications made to that point in the current transaction are rolled back, including any that were made by the trigger.

The trigger continues executing any remaining statements after the ROLLBACK statement. If any of these statements modify data, the modifications are not rolled back.

A ROLLBACK in a trigger closes and deallocates all cursors that were declared and opened in the batch containing the statement that fired the trigger. This includes cursors declared and opened in stored procedures called by the batch that fired the trigger. Cursors declared in a batch prior to the batch that fired the trigger are only closed. However, STATIC or INSENSITIVE cursors are left open if:

CURSOR_CLOSE_ON_COMMIT is set OFF.

The static cursor is either synchronous or a fully populated asynchronous cursor.

Instead of using ROLLBACK TRANSACTION, the SAVE TRANSACTION statement can be used to execute a partial rollback in a trigger.

——————-

Ok – so how do you prevent a rollback in your trigger from affecting the outer transaction/statement?

As the article says, use the “Save Transaction” statement, or just define your action as its own local transaction with “Begin Transaction” and then Commit or Rollback internally as appropriate, e.g.:

… trigger MyTrigger

declare @MyTransactionName varchar(32) = ‘My Local Tran’

Begin Transaction @MyTransactionName
… do something
… if error then
Rollback Transaction @MyTransactionName
… else if everything worked, then
Commit Transaction @MyTransactionName

Note that if you fail to properly close your transaction, an error will occur in the calling application when the SQL Server engine finishes the outer transaction and determines that the transaction counter isn’t 0.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following