Trigger Specifics
(Page 1 of 4 )
Last week, we began our discusion of triggers. This week, we will cover specific triggers, including nested and recursive triggers, instead-of triggers, and more. The second of several parts, 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).
Handling Changes on Multiple Records
The following example is a trigger designed to record the name of the user that changed the status of an order in the ActivityLog table, along with some additional information:
Create Trigger dbo.trOrderStatus_U_1
On dbo.OrderHeader
After Update -- For Update
As
declare @intOldOrderStatusId int,
@intNewOrderStatusId int
If Update (OrderStatusId)
Begin
select @intOldOrderStatusId = OrderStatusId from deleted
select @intNewOrderStatusId = OrderStatusId from inserted
Insert into dbo.ActivityLog(
Activity,
LogDate,
UserName,
Note)
values (
'OrderHeader.OrderStatusId',
GetDate(),
User_Name(),
'Value changed from '
+ Cast( @intOldOrderStatusId as varchar)
+ ' to '
+ Cast((@intNewOrderStatusId) as varchar)
)
End
This method is far from perfect. Can you detect the problem? It records the user who has changed the status of an order only when the user changes no more than a single order:
select @intOldOrderStatusId = OrderStatusId from deleted
Let me remind you that if the Select statement returns more than one record, the variable(s) will be filled with values from the last record. This is sometimes all that is required. If you have restricted access to the table and the only way to change the status is through a stored procedure (which allows only one record to be modified at a time), then this is sufficient.
Unfortunately, there is always a way to work around any restriction and possibly issue an Update statement that will change the status of all tables. The following is the proper solution:
Alter Trigger dbo.trOrderStatus_U
On dbo.OrderHeader
After Update -- For Update
As
If Update (OrderStatusId)
begin
Insert into ActivityLog
( Activity,
LogDate,
UserName,
Note)
Select 'OrderHeader.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
end
In this case, a set operation is used and one or more records from the Deleted and Inserted virtual tables will be recorded in ActivityLog.
Next: Nested and Recursive Triggers >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
|
 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.
|
|
