Transaction Management in Triggers


A trigger is always part of the transaction that initiates it. That transaction can be explicit (when SQL Server has executed Begin Transaction). It can also be implicit—basically, SQL Server treats each Transact-SQL statement as a separate transaction that will either succeed completely or fail completely.

It is possible to abort the entire transaction from inside the trigger by using Rollback Transaction. This command, shown in action next, is valid for both implicit and explicit transactions:

 Alter Trigger trOrderStatus_U On dbo.[Order] After Update    --For Update As      If @@Rowcount = 0           Return      If Update (OrderStatusId)      Begin           Insert into ActivityLog( Activity,                                     LogDate,                                     UserName,                                     Note)           Select   'Order.OrderStatusId',                     GetDate() ,                     USER_NAME(},                    'Value changed from '                    + Cast( d.OrderStatusId as varchar)                    + ' to '                    + Cast( i.OrderStatusId as varchar)          From deleted d inner join inserted i          On d.OrderId = i.OrderId          If @@Error <> 0          Begin               RAISERROR ("Error in trOrderStatus_U", 16, 1}               Rollback Transaction          End End 

In this trigger, SQL Server investigates the presence of the error and rolls back the complete operation if it is unable to log changes to the ActivityLog table.

The processing of Rollback Transaction inside a trigger differs from its processing inside a stored procedure. It also differs in different versions of Microsoft SQL Server.

When a Rollback statement is encountered in a stored procedure, changes made since the last Begin Transaction are rolled back, but the processing continues.

In Microsoft SQL Server 2005, when a Rollback statement is executed within a trigger, a complete batch is aborted and all changes are rolled back. SQL Server continues to process from the beginning of the next batch (or stops if the next batch does not exist).

Microsoft SQL Server 2000, 4.2, and 7.0, and all versions of Sybase SQL Server behaved in this manner. In Microsoft SQL Server 6.0, execution was continued through the trigger, but the batch was canceled. Version 6.5 went to an opposite extreme; execution of both the trigger and the batch was continued. It was the responsibility of the developer to detect an error and stop further processing.

Note 

Statements in the trigger after the Rollback statement will he executed normally. Any modifications done after the rollback point will not he rolled hack. However, nested triggers will not he fired as a result of modifications after the rollback point.




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net