Data Points
Exploring SQL Server Triggers
John Papa
Contents
Triggers are one of the core tools available in relational databases such as SQL Server™ 2000. As one of the mainstays of SQL Server database programming, triggers also happen to be one of the topics that I get most of the questions about. In this month's installment of Data Points, I will explore the different trigger types that SQL Server 2000 makes available along with many of the features that they expose. When used properly, triggers can play a key role in a data model and in implementing enterprise-wide business rules. Triggers can be implemented to enforce business rules or referential data integrity in database applications. There are even types of triggers that open the doors to possibilities such as allowing data modifications to multiple base tables of a view.
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.
Figure 1 Trigger that Notifies on Price Change
|
CREATE TRIGGER tr_Products_U ON Products AFTER UPDATE
AS
DECLARE @sMsg VARCHAR(200),
@sProductName NVARCHAR(40),
@mOldPrice MONEY,
@mNewPrice MONEY
IF UPDATE(UnitPrice)
BEGIN
SELECT @sProductName = d.ProductName,
@mOldPrice = d.UnitPrice,
@mNewPrice = i.UnitPrice
FROM inserted i
INNER JOIN deleted d ON i.ProductID = d.ProductID
SET @sMsg = 'The price of ' + @sProductName
+ ' has changed from '
+ CONVERT(VARCHAR(10), @mOldPrice)
+ ' to ' + CONVERT(VARCHAR(10), @mNewPrice)
+ ' on ' + CONVERT(VARCHAR(30), getdate()) + '.'
EXECUTE prSendMail 'testX@microsoft.com', @sMsg,
'price change notification', 'testY@microsoft.com'
END
RETURN
GO
|
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.
Figure 2 The prSendMail Stored Procedure
|
CREATE PROCEDURE prSendMail
@sTo VARCHAR(500), @sBody VARCHAR(8000), @sSubject VARCHAR(500),
@sFrom VARCHAR(500)
AS
DECLARE @nCDO INT,
@nOLEResult INT, @nOutput INT, @sSource VARCHAR(255),
@sDescription VARCHAR(255)
EXECUTE @nOLEResult = sp_OACreate 'CDO.Message', @nCDO OUT
IF @nOLEResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT
SELECT 'Error Creating CDO.Message Object',
hResult=convert(varbinary(4),@nOLEResult),
Source=@sSource,
Description=@sDescription
RETURN
END
EXECUTE @nOLEResult = sp_OASetProperty @nCDO, 'To', @sTo
IF @nOLEResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT
SELECT 'Error Executing CDO.To Property',
hResult=convert(varbinary(4),@nOLEResult),
Source=@sSource,
Description=@sDescription
RETURN
END
EXECUTE @nOLEResult = sp_OASetProperty @nCDO, 'From', @sFrom
IF @nOLEResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT
SELECT 'Error Executing CDO.From Property',
hResult=convert(varbinary(4),@nOLEResult),
Source=@sSource, Description=@sDescription
RETURN
END
EXECUTE @nOLEResult = sp_OASetProperty @nCDO, 'Subject', @sSubject
IF @nOLEResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT
SELECT 'Error Executing CDO.Subject Property',
hResult=convert(varbinary(4),@nOLEResult),
Source=@sSource,
Description=@sDescription
RETURN
END
EXECUTE @nOLEResult = sp_OASetProperty @nCDO, 'TextBody', @sBody
IF @nOLEResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT
SELECT 'Error Executing CDO.TextBody Property',
hResult=convert(varbinary(4),@nOLEResult),
Source=@sSource,
Description=@sDescription
RETURN
END
--Call Send method of the object
EXECUTE @nOLEResult = sp_OAMethod @nCDO, 'Send', Null
IF @nOLEResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT
SELECT 'Error Executing CDO.Send Method',
hResult=convert(varbinary(4),@nOLEResult),
Source=@sSource, Description=@sDescription
RETURN
END
--Destroy CDO
EXECUTE @nOLEResult = sp_OADestroy @nCDO
RETURN @nOLEResult
GO
|
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.
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.