Designing and Modifying Triggers - Transaction Management in Triggers
(Page 4 of 4 )
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 be executed normally. Any modifications done after the rollback point will not be rolled back. However, nested triggers will not be fired as a result of modifications after the rollback point.
Please check back next week for the conclusion of this article.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter nine of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 5 
