It is very important to evaluate your options when choosing to employ a trigger. In deciding whether you'll use triggers, the key is to balance functionality, scalability, maintenance, and performance. I'll explore some of these factors and offer some insight on how to weigh them. In addition, I'll explain the foundation of SQL Server triggers and the features that they expose. I will examine the differences between the two types of triggers while demonstrating the places where each can be useful. Then I'll show examples of using AFTER triggers that serve an important role in SQL Server-based applications. I will also walk through the use of triggers to enforce referential integrity and to implement business rule validation at the database level. Before wrapping up, I will discuss performance considerations, features unique to triggers, and some limitations to keep in mind.
Trigger Types
The first ingredient in properly employing triggers is to understand the differences between AFTER and INSTEAD OF triggers. AFTER triggers are the same type of trigger that is available in previous versions of SQL Server. They are also known as "FOR triggers" or even simply as "triggers" since they were the only type of trigger available prior to SQL Server 2000.
Let's first look at FOR triggers. You'll notice that the following trigger is created using the FOR keyword:
CREATE TRIGGER tr_Employees_U on Employees FOR UPDATE AS
IF UPDATE(lastname)
BEGIN
RAISERROR ('cannot change lastname', 16, 1)
ROLLBACK TRAN
RETURN
END
GO
This trigger, tr_Employees_U will execute after an UPDATE statement is run against the Employees table. It will then check to see if the lastname field was modified and if so it will raise an error and undo the changes that the UPDATE statement made. To accomplish this, this code uses three very common features of triggers: UPDATE, RAISERROR, and ROLLBACK TRANS. I will explain these in more detail later.
That syntax is also acceptable in older versions of SQL Server. However, now that there are two types of triggers in SQL Server 2000, I prefer to refer to FOR triggers as AFTER triggers. Thus, for the remainder of this article I will refer to either AFTER or INSTEAD OF triggers.
AFTER triggers execute following the triggering action, such as an insert, update, or delete. The example trigger you just saw will fire after an UPDATE statement has been executed against the Employees table. Therefore, the trigger does not fire until the row or rows have been inserted and constraints have been checked and passed. Basically, AFTER triggers fire very late in the process.
INSTEAD OF triggers, introduced with SQL Server 2000, are intended to be employed in different situations. INSTEAD OF triggers fire in place of the triggering action. For example, if an INSTEAD OF UPDATE trigger exists on the Employees table and an UPDATE statement is executed against the Employees table, the UPDATE statement will not change a row in the Employees table. Instead, the UPDATE statement causes the INSTEAD OF UPDATE trigger to be executed, which may or may not modify data in the Employees table.
The following trigger will fire in place of any UPDATE statement made against the Employees table:
CREATE TRIGGER tr_Employees_U_insteadof ON Employees INSTEAD OF UPDATE AS
IF UPDATE(lastname)
BEGIN
RAISERROR ('cannot change lastname (source = instead of)', 16, 1)
ROLLBACK TRAN
RETURN
END
ELSE
-- Go ahead and do the update or some other business rules here
GO
Like the AFTER trigger you saw earlier, this trigger prevents changes from being made to the lastname field. However, it implements this business rule differently than the previous example. Because the INSTEAD OF trigger fires in place of the UPDATE statement, the INSTEAD OF trigger then evaluates if the business rule test passes or not. If the business rule test passes, in order for the update to occur the INSTEAD OF trigger must explicitly invoke the UPDATE statement again.
In this situation, the AFTER trigger would be a more efficient mechanism to enforce this business rule because there is no overwhelming benefit of the INSTEAD OF technique and the AFTER trigger requires less complex code logic. This is especially true if you wanted the INSTEAD OF trigger to perform the original update since it would have to reconstruct the statement. This is possible using the UPDATE function and the inserted and deleted tables, but it is much more complicated than necessary.
One question I get frequently is whether you can have multiple triggers hanging off of a single table. There can be several AFTER triggers associated with a single table, even multiple AFTER triggers on the same action query type (UPDATE, INSERT, or DELETE). For example, the Employees table could have two distinct AFTER UPDATE triggers associated with the table, each of which performs a different set of tasks. In fact, there could be three or four, or even more AFTER triggers associated with the same table; they will all fire following the UPDATE statement. (Keep in mind that even though there could be several triggers associated with the same table, they each must have a unique name.)
You can even tell SQL Server which order you want the AFTER triggers to fire by using the system stored procedure sp_settriggerorder. However, I have rarely wanted to have more than one trigger hanging from the same table upon the same action query, and even when I did, I didn't care about the sequence they fired in. Most often you can combine all of your logic into a single AFTER trigger. But in cases in which it is easier to break logic into separate code blocks, different triggers would do the trick.
Note, however, that there can be only one INSTEAD OF trigger on the same table and same action query type. Remember that INSTEAD OF triggers replace the originating action query, so if there was an INSTEAD OF UPDATE trigger on the Employees table it would fire in place of the UPDATE query statement. However, there cannot be two INSTEAD OF UPDATE triggers hanging from the Employees table. Therefore, there can be at most three INSTEAD OF triggers associated with a table; one for each action query type—INSERT, UPDATE, and DELETE.
Firing Sequence and Referential Integrity
One of the most important factors in understanding the differences between AFTER and INSTEAD OF triggers is to understand their firing sequences. An AFTER trigger does not fire until after the action query that invoked the AFTER trigger has made its data modification. In addition, it is important to know what other events occur surrounding the AFTER trigger. If the Employees table had a single AFTER UPDATE trigger associated with it and an UPDATE statement was executed on the table, the following sequence of events would unfold.
- The following statement is executed:
UPDATE Employees SET lastname = 'papa' WHERE lastname = 'king'
- All constraints are enforced.
- All declarative referential integrity (DRI) constraints are enforced (via foreign keys).
- The inserted and deleted tables are created for use within the trigger.
- The triggering action (in this case the UPDATE statement) is executed.
- The AFTER UPDATE trigger executes.
These steps follow sequence as long as each step succeeds. For example, if a NOT NULL constraint is violated, then the DRI is not checked. After reviewing this firing sequence for AFTER triggers, it is clear why referential integrity cannot be enforced through an AFTER trigger if the same reference is being enforced via DRI with a foreign key constraint: the trigger would never fire. An AFTER trigger cannot be used to execute any logic that might prevent constraint violation from occurring since the trigger isn't fired until all constraints have been checked.
It is not generally good practice to enforce DRI through a trigger unless there needs to be some more involved business logic surrounding the referential integrity check. In those cases, the foreign key would have to be removed and the trigger would enforce the referential integrity. However, I recommend against using triggers to enforce referential integrity since this approach is less efficient than using a foreign key. It means the data is written to the table and then has to be rolled back.
Assuming that there is only a single INSTEAD OF UPDATE trigger on the Employees table, let's set up another scenario to demonstrate the firing sequence of INSTEAD OF triggers. In this case, the sequence would be as follows:
- The following statement is executed:
UPDATE Employees SET lastname = 'papa' WHERE lastname = 'king'
- The inserted and deleted tables are created for use within the trigger.
- The INSTEAD OF trigger executes.
- Any subsequent actions executed may be kicked off by the logic within the INSTEAD OF trigger.
There can be an AFTER UPDATE trigger and an INSTEAD OF UPDATE trigger on the same table. In this case, the INSTEAD OF trigger would fire and if it executed an UPDATE statement on the same table, then the events would fire as noted previously in the AFTER trigger's sequence of events.
When planning how you'll enforce data integrity you should first perform any entity integrity checks with primary key and unique key constraints. Entity integrity checks ensure that unique rows exist within the entity (the table). Domain integrity checks should then be enforced through constraints such as CHECK constraints. Domain integrity ensures that a column has a valid value from a specific domain and makes sure it has a value at all if it is required to (are nulls allowed or not). For example, a column called sTrafficLightColor which is defined as a VARCHAR(6) might have a CHECK constraint on it that ensures that the value is either red, yellow, or green. Finally, referential integrity should be enforced via foreign key constraints if at all possible.
I'll reiterate that triggers are less efficient than foreign keys at enforcing referential integrity. However, there are some situations in which AFTER triggers can offer value in this area. One example is when a business rule exists that requires a complex referential integrity check where a value in a table must reference a value in one of two parent tables. For example, assume that there is a tblBankAccount table and tblBrokerageAccount table, both of which have a column called nAccountNumber. This nAccountNumber field is defined as a CHAR(10) in both tables and is each table's primary key.
Now, assume there is a third table called tblTransaction which contains a required field called nAccountNumber. Normally, a foreign key constraint would be placed on this field to its parent table, but in this case there are two parent tables. This type of complex referential integrity can be validated using an AFTER trigger that checks to make sure that any value inserted or updated into the tblTransaction.nAccountNumber column is either a valid account number from tblBankAccount or tblBrokerageAccount. A foreign key constraint can only match one or more columns between two tables, while an AFTER trigger, despite being less efficient than a foreign key, has more possibilities. In this case the slight performance reduction might be less important than the gain you realize by enforcing the business rule.
Another reason AFTER triggers can be useful for enforcing complex constraints is that they can reference several tables and columns whereas CHECK constraints can only access the current row in the current table. AFTER triggers can also cascade changes throughout other tables, but cascading referential integrity constraints are more efficient than triggers in this situation.
Special Tables
There are two very special tables that can exist only within triggers: the inserted and deleted tables. These tables are used to store the before and after state of the table that was affected by the INSERT, UPDATE, or DELETE statement that caused the trigger to fire in the first place. For example, if an AFTER UPDATE trigger is associated with the Products table it can be used to check if a product's price is being changed. If the price is being changed, the trigger could perform a variety of business rules such as making sure the price change is not exceeding a set percentage. If the price change exceeds a specific percentage, the change could be rolled back and an error message could be raised. Another scenario would be to allow the price change but to notify a supervisor when the change occurs. The trigger in Figure 1 demonstrates this technique.
The AFTER trigger, called tr_Products_U in Figure 1, shows the use of the UPDATE function as well as the inserted and deleted tables. The UPDATE function accepts a single parameter that represents a column in the table. The function returns a bit value to indicate whether the column's value has been modified; it's only available inside of triggers. The inserted and deleted tables are crucial elements of trigger logic and here they store the before and after picture of what happened to the Products table. For example, if the following UPDATE statement ran, the inserted and deleted tables would contain a single row representing ProductID #1.
UPDATE Products SET UnitPrice = UnitPrice - 1 WHERE ProductID = 1
Both the inserted and deleted tables would contain all of the columns that the Products table contains. As noted, both the inserted and deleted tables would contain a single row representing ProductID #1 but with one slight difference. The value for the UnitPrice column would be different in these tables, thus reflecting the price change. Fortunately, you do not have to compare these values to see if a column's value has changed; the UPDATE function does this for you.
In the scenario in which the price has changed, an e-mail message is formatted and sent using a custom stored procedure called prSendMail (see Figure 2). In the trigger tr_Products_U, the values for both the old UnitPrice and the new UnitPrice are gathered by joining the inserted and deleted tables and then retrieving the UnitPrice from each of the tables.
In an AFTER UPDATE trigger where at least one row was updated, both the inserted and deleted tables will contain the same number of rows. In an AFTER DELETE trigger, only the deleted table will contain data since there are no new values. On the other hand, in an AFTER INSERT trigger, only the inserted table will contain data since no existing record was deleted or modified. These tables are often at the heart of many triggers. One drawback of the inserted and deleted tables is that columns defined as a binary datatype including text, ntext, and images will not be represented.
The stored procedure shown in Figure 2 uses the system stored procedures that allow a stored procedure to invoke COM objects including SQLDMO, ADODB, or the CDOSYS library. This allows the stored procedure to enhance its functionality by reusing existing COM objects. I created this stored procedure as an example of how to roll your own mail sending function from SQL Server without having to rely on the xp_sendmail stored procedure. That procedure has some drawbacks including that it is not available in a SQL Server clustered environment. (I will save this topic for a future column.)
Performance
I often get asked about the performance of triggers and how to tune them. Actually, triggers themselves do not carry much overhead and, in general, are quite responsive. Most performance issues can be primarily attributed to the logic contained within the triggers. For example, if a trigger creates a cursor and then loops through hundreds of rows, you could expect a slowdown. If a trigger runs several SQL statements against other tables outside of the inserted and deleted tables, you should also expect a slowdown proportionate to the speed of the SQL statements contained within the trigger. A good rule is to keep the logic contained within a trigger simple and avoid using cursors, executing SQL statements against other tables, and other tasks that normally cause performance hits. SQL is optimized for rowset logic and as such the use of cursors should be avoided when it is possible to replace that logic with a SQL statement (even if it appears to be more complex).
The code within a trigger is now compiled and stored, just like a stored procedure or standard SQL batch. Because the trigger's code is repeated every time the action query on its table is executed, the trigger runs faster on subsequent executions, just like a stored procedure. This is because SQL Server stores the source for the trigger, compiles the trigger into an execution plan, and stores it in memory. This memory is allocated by SQL Server and is managed using a first in, first out plan that helps keep the most frequently run stored procedures, triggers, and other SQL batches running smoothly.
It is generally not a good idea to return results from a trigger. This can cause performance hits and could cause any applications that execute queries against the table to be adversely affected if they are not expecting a resultset to be returned from a trigger. For example, if an AFTER UPDATE trigger executes a SELECT statement that returns 100 rows, any calling application that updates that table will get back a 100-row rowset that they most likely did not expect. Every once in a while, though, returning a rowset from a trigger is worth considering. One example is when you're returning the IDENTITY value from an AFTER INSERT trigger. For more information on this scenario, see the July 2002 installment of Data Points.
To prevent rowsets from being returned from triggers by accident, do not include SELECT statements or variable assignments. If variable assignments are made, you could first execute the SET NOCOUNT ON statement to eliminate the return of a resultset.
Until Next Time
This month's column walked through the foundation of triggers and explained several applications of AFTER triggers. I also discussed the firing sequence of the two trigger types since it is crucial to understanding how the types operate. However, there are many other features of triggers I will explore in upcoming Data Points columns such as nested transactions, the auditing of data, cascading referential integrity, preparing triggers to handle changes to multiple rows in a table, and the use of the COLUMNS_UPDATED function. INSTEAD OF triggers are a great feature that allow you to perform complex action queries in place of a single action query on a table or a view. This column showed triggers that were written to handle scenarios in which a single row was affected.
month I will expand on last month's column—the foundation of SQL Server™ triggers and the features that they expose. I have targeted this column in response to many of the questions I have received regarding triggers. I'll begin with INSTEAD OF triggers and the differences between them and AFTER triggers. I'll then discuss some situations in which to use INSTEAD OF triggers including allowing updates to multiple tables through views. Finally, I'll discuss how to handle transactions in triggers, writing triggers to handle changes to multiple rows in a table, and determining which columns were modified within triggers.
INSTEAD OF Triggers
AFTER triggers (also known as FOR triggers) execute following the triggering action, such as an insert, update, or delete. For example, an AFTER trigger on an Employees table will fire after an UPDATE statement has been executed against the Employees table. Thus, the trigger does not fire until the row or rows have been inserted and constraints have been checked and passed. INSTEAD OF triggers are inherently different from AFTER triggers because INSTEAD OF triggers fire in place of the triggering action. So using the same example, if an INSTEAD OF UPDATE trigger exists on the Employees table and an UPDATE statement is executed against the Employees table, then the UPDATE statement will not change a row in the Employees table. Instead, the UPDATE statement is used to kick off the INSTEAD OF UPDATE trigger, which may or may not modify data in the Employees table.
So how do you determine the right time and place to use an INSTEAD OF trigger? Several key factors are worth considering when making this decision. AFTER triggers are more commonly used in situations where actions must be taken following data modifications on tables. For example, an AFTER trigger could be used to log any data updates to a separate auditing table. INSTEAD OF triggers could do the same job, but they are less efficient in this particular scenario since the update will be allowed exactly as it occurred after writing to the audit table.
In general, in any situation where the data modification will not be affected, an AFTER trigger is more efficient. An AFTER trigger is also a great choice when the data modification is evaluated and is either allowed to commit as a whole or denied entirely. For example, a rule could exist that any change to a product's price of more than 30 percent in the Products table must be undone. An AFTER trigger could do the job here nicely, using the inserted and deleted tables to compare the price of the product and then roll back the transaction if need be. These are ideal situations for AFTER triggers, but sometimes INSTEAD OF triggers are better.
INSTEAD OF triggers are a great feature that allow you to perform complex action queries in place of a single action query on a table or a view. Unlike AFTER triggers, which can only be created against tables, INSTEAD OF triggers can be created against both tables and views. I have often been asked how to resolve the situation in which there is a view that represents a join of multiple tables and you want to allow an update to the view. If the view exposes the primary key and required fields of a base table, it is often simple to update the view's base table. However, when there are multiple tables represented in a join, the update logic is more complicated than a single UPDATE statement. So how do you resolve this situation using alternative tools? One solution is to place an INSTEAD OF UPDATE trigger on the view. INSTEAD OF triggers can be defined on views with one or more tables. The INSTEAD OF trigger can then extend the type of modifications that will take place on the multiple base tables.
For example, if a view joins the Customers, Products, Orders, and Order Details tables together to show all of the data on a screen through an application, updates could be allowed to take place through this view. Assuming a view exists that joins these four tables in the Northwind database and is named vwCustomersOrdersOrderDetailsProducts, it could look like Figure 1.
The vwCustomersOrdersOrderDetailsProducts view joins the four tables and exposes a sampling of fields from each of the tables. One key to remember when designing views that will have INSTEAD OF UPDATE triggers is that it is helpful to include the primary key fields from each table in the SELECT statement. Even if these fields are not used in the application, they can be used in the INSTEAD OF trigger to locate the row(s) that were intended to be modified and then make the appropriate changes in the base tables. Let's assume that you want to allow updates to this view to funnel down to the base tables on the non-key fields. Code would then have to be written in the INSTEAD OF UPDATE trigger to update the CompanyName in the Customers table, the OrderDate in the Orders table, the UnitPrice and Quantity in the Order Details table, and the ProductName in the Products table. At this point using an AFTER trigger won't cut it, but an INSTEAD OF trigger is a good option (see Figure 2).
Notice that the code in the INSTEAD OF UPDATE trigger in Figure 2 (tr_vwCustomersOrdersOrderDetailsProducts_IO_U) contains four UPDATE statements. Each targets one of the base tables with the purpose of modifying the exposed non-key fields. The key fields for each base table are joined in the UPDATE statements to the corresponding fields that the view exposes. This allows the UPDATE statements to locate the appropriate rows in the underlying tables and update only those rows. The following UPDATE statement puts this INSTEAD OF trigger to the test:
UPDATE vwCustomersOrdersOrderDetailsProducts
SET Quantity = 100,
UnitPrice = 20,
CompanyName = 'Fake Name',
OrderDate = '11/23/2001',
ProductName = 'Widget'
WHERE OrderID = 10265
AND ProductID = 17
If you examine the values in the underlying tables (either through the view or in the tables themselves) it is evident that the values have been updated. Of course, several modifications could be made to this INSTEAD OF UDPATE trigger to yield different results. For example, it is not a requirement to write the INSTEAD OF trigger to modify all four base tables. Therefore, one or more of the UDPATE statements contained within the trigger could be removed. Assuming that the trigger was intended only to update the Order Details values, it could be modified to update only those fields in the Order Details table—ignoring any changes that were attempted on the other base tables. In this situation, no error is raised nor are the changes made to Customers, Products, or Orders tables. Of course, an error could be raised if one of these fields was updated. The UPDATE and COLUMNS_UPDATED functions are ideal for checking what fields have been modified, as I'll demonstrate later in this column.
Figure 2 also demonstrates how to prepare a trigger to deal with changes to multiple rows. Note how the UPDATE statements join the inserted table to each base table on the key field. This ensures that the updates will occur on all rows that were modified by the original UPDATE statement on the view. This could be done by looping through the rows in the inserted table, too. However, it is a good idea to avoid using cursors in general, and especially when using triggers. SQL Server is built to handle data in sets while cursors handle rows one at a time. Using cursors in a trigger can slow down your application, so it's better to use a more efficient alternative like the one in Figure 2 or a subquery.
Another way that this INSTEAD OF UPDATE trigger could be modified is to make it also fire on INSERT and DELETE statements on the view. This would mean the trigger would likely perform an INSERT or a DELETE where appropriate. Keep in mind, however, that a DELETE could remove several records depending on how the trigger is written. Therefore, it is critical that the requirements of the trigger be reviewed and thorough testing be performed before implementation. An INSTEAD OF INSERT trigger could be written on this view so that it inserts a new customer, order, order detail, and product. It could also be written to determine if the customer was new before inserting a new one (and so on with the other records). The opportunities are numerous when deploying INSTEAD OF triggers, but of course it is essential that the trigger is designed to resolve the appropriate requirement.
Usually when an UPDATE statement that references a table attempts to set the value of a computed, identity, or timestamp column, an error is generated because the values for these columns must be determined by SQL Server. These columns must be included in the UPDATE statement to meet the NOT NULL requirement of the column. However, if the UPDATE statement references a view with an INSTEAD OF UPDATE trigger, the logic defined in the trigger can bypass these columns and avoid the error. To do so, the trigger must not try to update the values for the corresponding columns in the base table (leave them out of the SET clause of the UPDATE statement). When a record is processed from the inserted table, the computed, identity, or timestamp column can contain a dummy value to meet the NOT NULL column requirement, but the INSTEAD OF UPDATE trigger ignores those values and the correct values are set by SQL Server.
Updating Derived Columns
INSTEAD OF triggers are also commonly used to UPDATE the base columns in calculated columns. For example, assume that a view exists called vwOrdersOrderDetailsProducts as shown here:
CREATE VIEW vwOrdersOrderDetailsProducts
AS
SELECT o.OrderID,
o.OrderDate,
od.UnitPrice * od.Quantity AS ExtendedPrice,
p.ProductID,
p.ProductName
FROM Orders o
INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID
GO
This view exposes a calculated column called ExtendedPrice that cannot be updated directly because it does not resolve itself to a single column in a table. However, you could implement a business rule that says that if the ExtendedPrice field is updated through this view, then Quantity should not change but the UnitPrice is modified. (I know this rule is a bit odd, but bear with me on this.) An INSTEAD OF UPDATE trigger could be written to enforce this business rule using the code shown here: CREATE TRIGGER tr_vwOrdersOrderDetailsProducts_IO_U
ON vwOrdersOrderDetailsProducts
INSTEAD OF UPDATE
AS
UPDATE [Order Details]
SET UnitPrice = i.ExtendedPrice / Quantity
FROM inserted i
INNER JOIN [Order Details] od ON i.OrderID = od.OrderID AND
i.ProductID = od.ProductID
GO
This code shows how to replace an update to a calculated column with logic in INSTEAD OF triggers. Assuming that the Quantity for a product on a particular order is 100 and the ExtendedPrice is updated to 200, the new UnitPrice value will be 2. In this scenario, when the UPDATE statement is executed that modifies the ExtendedPrice column, the net effect is that the UnitPrice is set to the ExtendedPrice value divided by the Quantity. The following code can be used to test this situation: UPDATE vwOrdersOrderDetailsProducts SET ExtendedPrice = 200 WHERE OrderID = 10265 AND ProductID = 17
Checking for Changes
The UPDATE and COLUMNS_UPDATED functions are available within both types of triggers to allow the trigger to determine which columns were modified by the triggering action statement. For example, the following trigger prevents any modifications to the lastname column in the Employees table. Here, the UPDATE function is used to determine if a modification was made to the column. If so, then an error is raised using the RAISERROR function and the transaction is rolled back, which undoes any changes that were made. The UPDATE function works in both the AFTER and INSTEAD OF triggers, but not outside of the triggers:
CREATE TRIGGER tr_Employees_U on Employees AFTER UPDATE AS
IF UPDATE(lastname)
BEGIN
RAISERROR ('cannot change lastname', 16, 1)
ROLLBACK TRAN
RETURN
END
GO
The UPDATE function is designed to determine if a single column was modified by an INSERT OR UPDATE statement. UPADATE (column) is the standard method used to check for updates. However, it becomes less efficient when the need arises to check if multiple columns were affected by an INSERT or UPDATE. This is where the COLUMNS_UPDATED function steps into the spotlight. The COLUMNS_UPDATED function returns a bitmask to evaluate if specific columns were modified. The bitmask contains a bit for every column in the table that was modified, in the order that the columns are defined in the table's schema. If the column was modified, the bit's value is 1; otherwise it is 0. Unlike the conventional way to read bytes going right to left, the bitmask reads from left to right. For example, the following code shows a trigger on the Order Details table that checks to see if both the Quantity and UnitPrice fields are modified:
CREATE TRIGGER tr_OrderDetails ON [Order Details] AFTER UPDATE
AS
IF (COLUMNS_UPDATED() = 12)
BEGIN
RAISERROR ('Cannot change both UnitPrice and Quantity at the
same time', 16, 1)
ROLLBACK TRAN
END
GO
If both fields are modified, an error is raised and the transaction is rolled back. For the Order Details table, the COLUMNS_UPDATED function returns a single byte with the first five bits representing the columns in the Order Details table. Since the situation called for determining if only the third and fourth columns were modified, it looked to see if only those bits were set to 1. When the third and fourth bits are turned on, it looks like this: 00110. Since this bitmask represents the powers of 2, the first bit represents 1, the second represents 2, the third represents 4, the fourth represents 8, and the fifth represents 16 (yes, this is the reverse order of bits in a normal binary number). Therefore, the bitmask value that means only the UnitPrice and Quantity were changed is 00110, which equates to the integer value of 12 (4 + 8). Note that this trigger only rolls back the transaction if the UnitPrice and Quantity fields were modified. If any other fields were modified, the bitmask would be different and thus not equal to the integer value of 12. If the trigger were modified to prohibit changes to these two fields even if other fields were modified, it could be rewritten like so:
ALTER TRIGGER tr_OrderDetails ON [Order Details] AFTER UPDATE
AS
IF (COLUMNS_UPDATED() & 12 >= 12)
BEGIN
RAISERROR ('Cannot change both UnitPrice and Quantity
at the same time', 16, 1)
ROLLBACK TRAN
END
GO
Notice how the COLUMNS_UPDATED function is now checked to see if the value is greater than or equal to 12. [Editor's Update - 12/6/2004: This trigger incorrectly compared the sum of the bit mask to the sum of the 3rd and 4th columns using the condition "COLUMNS_UPDATED() >= 12". Any time a column after the 4th column was updated, this trigger would incorrectly raise an error. The code for the trigger has been corrected to take this into consideration by using the binary & operator.] If you modified the UnitPrice, Quantity, and Discount columns, the bitmask would be 00111, resulting in an integer value of 28 (4 + 8 + 16). When there are more than eight columns in a table, this function returns the bitmasks in chunks of multiple bytes with the first eight columns in the first byte, columns 9 through 16 in the second byte, and so on. This function is more useful in determining which columns were updated than the UPDATE function for each column.
As demonstrated earlier, triggers can roll back transactions if a specific business rule is satisfied. When a trigger that contains a rollback is executed from a SQL batch the entire batch is canceled. Thus, all data that was modified by the triggering action is rolled back by the trigger's ROLLBACK TRANSACTION statement. However, a rollback does not cause the trigger to stop executing statements. Any statements following a ROLLBACK TRANSACTION statement are executed, which is fine, but there are consequences to putting statements after a ROLLBACK TRANSACTION. Specifically, as a trigger continues to execute any remaining statements after the rollback, any modifications that happen after the rollback are not rolled back. This occurs because once a ROLLBACK TRANSACTION has been issued within the trigger, the transaction is closed. Thus, when a new action query statement is executed, a new transaction begins separate from the original. Therefore, it is generally not recommended that you put any statements after a ROLLBACK TRANSACTION.
Just as a rollback does not automatically exit the trigger, it also does not raise an error automatically. If the rollback must occur and an error must be raised, the RAISERROR statement should follow the rollback immediately before exiting the trigger code.
Wrapping Up
There are other noteworthy characteristics of INSTEAD OF triggers. If an INSTEAD OF trigger on a table fires an action query that causes a data modification on the same table that would normally fire the same INSTEAD OF trigger, the trigger is not called recursively. So, if an INSTEAD OF UPDATE trigger existed on the Employees table and this trigger had code inside of it that updated the Employees table, it would not continue to recursively call the same INSTEAD OF trigger. It would get ugly fast if this kind of recursion were allowed. Another difference between INSTEAD OF triggers and AFTER triggers is that Text, Ntext, and Image columns can appear in the inserted and delete tables in INSTEAD OF triggers. These binary fields will show up as VARCHAR values, however, in the INSTEAD OF trigger's inserted and deleted tables, where applicable, and not as their original datatypes.
There is a useful stored procedure for examining triggers—the sp_helptrigger system stored proc. It returns the type of triggers defined on a table that you pass to the stored proc. Using this, you can see what triggers are associated with a table, what actions cause them to fire, and if they are AFTER or INSTEAD OF triggers.
In the past two columns I've explored many facets of AFTER and INSTEAD OF triggers. Of course, there are other scenarios in which they are useful and times they are not recommended. One situation in which triggers are not efficient is when the trigger must perform queries against other tables. In these cases, the performance of the trigger and the triggering action query may suffer. Triggers are great tools when used appropriately, but be sure to test your application thoroughly when using them.
Send your questions and comments for John to mmdata@microsoft.com.
John Papa is a baseball fanatic who spends most of his summer nights rooting for the Yankees with his two little girls, wife, and faithful dog, Kadi. He has authored several books on ADO, XML, and SQL Server and can often be found speaking at industry conferences such as VSLive. You can reach him at mmdata@microsoft.com.
浙公网安备 33010602011771号