New information has been added to this article since publication.
Refer to the Editor's Update below.
Data Points
Exploring SQL Server Triggers: Part 2
John Papa
Code download available at:DataPoints0401.exe(111 KB)
Contents
INSTEAD OF Triggers
Updating Derived Columns
Checking for Changes
Wrapping Up
This 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.
Figure 1 View Joining Customers and Their Order Details
CREATE VIEW vwCustomersOrdersOrderDetailsProducts AS SELECT c.CustomerID, c.CompanyName, o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity, od.Discount, p.ProductID, p.ProductName FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID INNER JOIN [Order Details] od ON o.OrderID = od.OrderID INNER JOIN Products p ON od.ProductID = p.ProductID GO
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).
Figure 2 INSTEAD OF Trigger Updating a View
CREATE TRIGGER tr_vwCustomersOrdersOrderDetailsProducts_IO_U ON vwCustomersOrdersOrderDetailsProducts INSTEAD OF UPDATE AS — Update the Customers UPDATE Customers SET CompanyName = i.CompanyName FROM inserted i INNER JOIN Customers c ON i.CustomerID = c.CustomerID — Update the Orders UPDATE Orders SET OrderDate = i.OrderDate FROM inserted i INNER JOIN Orders o ON i.OrderID = o.OrderID — Update the Order Details UPDATE [Order Details] SET UnitPrice = i.UnitPrice, Quantity = i.Quantity FROM inserted i INNER JOIN [Order Details] od ON i.OrderID = od.OrderID AND i.ProductID = od.ProductID — Update the Products UPDATE Products SET ProductName = i.ProductName FROM inserted i INNER JOIN Products p ON i.ProductID = p.ProductID GO
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.