Triggers - Full Syntax of After Triggers
(Page 4 of 4 )
The After trigger was the only type of trigger before SQL Server 2000. After triggers in SQL Server 2000 have the same syntax as before except that the keyword For is (optionally) replaced with After:
Create Trigger trigger_name
On table
[With Encryption]
{
{After | For { [Delete] [,] [Insert] [,] [Update] }
[With Append]
[Not For Replication]
As
sql_statement [...n]
}
|
{After | For { [Insert] [,] [Update] }
[With Append]
[Not For Replication]
As
{ If Update (Column)
[{And | Or} Update (Column)]
[...n]
| If (Columns_Updated()
{bitwise_operator}
updated_bitmask)
{ comparison_operator} column_bitmask [...n]
}
sql_statement [ ...n]
}
}
If a trigger is defined with the With Encryption clause, SQL Server encrypts it so that its code remains concealed. Keep in mind that you need to preserve the source code in a script outside SQL Server if you plan to modify it later.
The Not For Replication clause indicates that SQL Server should not fire a trigger during replication of the table.
The With Append clause is used only when the compatibility mode of SQL Server is set to a value less than 70. For more details, refer to SQL Server Books OnLine.
It is possible to determine which columns were updated during the Update operation. Transact-SQL includes two functions that you can use within the trigger—Update() and Columns_Updated():
If Update (column)
sql_statement [ ...n]
If (Columns_Updated() {bitwise_operator} updated_bitmask)
{comparison_operator} column_bitmask [...n]
sql_statement [ ...n]
You can now modify your previously used trigger to update only the fields that were changed:
Alter Trigger trEquipment_IU
On dbo.Equipment
After Insert, Update -- For Insert, Update
As
-- precalculate ModelSDX and MakeSDX field
-- to speed up use of SOUNDEX function
if Update(Model)
update dbo.Equipment
Set ModelSDX = SOUNDEX(Model)
where EqId IN (Select EqId from Inserted)
if Update(Make)
update dbo.Equipment
Set MakeSDX = SOUNDEX(Make)
where EqId IN (Select EqId from Inserted)
go
The Update() function might not perform exactly as you expect. In fact, it returns True for columns that were referenced during the Transact-SQL statement rather than for columns that were actually changed. For example, if you issue the following Update statement, SQL Server references the Make column of all records, and the trigger recalculates the SOUNDEX code in all records:
Update dbo.Equipment
Set Make = Make
TIP
This behavior might cause some problems for you if you forget about it. However, in some cases, you can use it to your advantage. For example, to speed up the upload of information to the table, you can temporarily disable triggers (see the “Disabling DDL Triggers” and “Disabling DML Triggers” sections near the end of this chapter). Later, when you want to execute the triggers (for example, to verify their validity and/or perform additional activities), you can use this feature to initiate triggers for records that are present in the table.
Too often, developers forget that the presence of a Default constraint in a column causes the Update() function to return True for that column during the execution of the Insert statement. This will occur even if the Insert statement did not reference the column itself.
The Columns_Updated() function operates with a bitmap that is related to the positions of columns. You can investigate its contents if you use an integer bitmask. To test whether the third column in a table was updated, you can use the following:
if Columns_Updated() & 3 = 3
print 'Column 3 was updated!'
The ampersand (&) is a binary and operator, with which you can test the value of the flag.
Naturally, hard-coding the order of columns does not make much sense. The real value of this function is as a means of looping through all the columns that were updated and performing specified actions.
The following trigger loops through columns and displays which ones were updated:
Create Trigger trEquipmentN_IU_2
-- list all columns that were changed
On dbo.EquipmentN
after Insert, Update
As
Set Nocount Off
declare @intCountColumn int,
@intColumn int
-- count columns in the table
Select @intCountColumn = Count(Ordinal_position)
From Information_Schema.Columns
Where Table_Name = 'EquipmentN'
Select Columns_Updated() "COLUMNS UPDATED"
Select @intColumn = 1
-- loop through columns
while @intColumn <= @intCountColumn
begin
if Columns_Updated() & @intColumn = @intColumn
Print 'Column ('
+ Cast(@intColumn as varchar)
+ ') '
+ Col_Name(Object_ID('EquipmentN'), @intColumn)
+ ' has been changed!'
set @intColumn = @intColumn + 1
End
Use the following statement to test this trigger:
Insert EquipmentN(Make, Model, EqTypeID) Values('Acme', '9000', 1)
You will notice that unlike an Update statement, an Insert statement will make changes to all columns.
Please check back next week for the continuation 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.
|
|
/ 8 
