Triggers are methods that SQL Server provides to the application programmer and database analyst to ensure data integrity. These methods are quite useful for those databases that will be accessed from a multitude of different applications, because they enable the database to enforce business rules instead of relying on the application software. n
SQL Server's capability to manage your information effectively stems from its capability to help you control the data in your system as it flows through the tables and application logic that you build into your application. You've seen how stored procedures enable you to execute logic on the server, and you've seen how you can implement rules and defaults to help further manage the information in the database.
SQL Server considers rules and defaults before information is written to the database. They are a sort of "prefilter" for information and can prevent an action against the data item based on their role in controlling the database activity.
Triggers, on the other hand, are "postfilters" that execute after the data update passes and SQL Server has considered the rules, defaults, and so on.
A trigger is a special type of stored procedure that SQL Server executes when an insert, modify, or delete operation is performed against a given table. Because triggers run after the operation would take effect, they represent the "final word" on the modification. If the trigger causes a request to fail, SQL Server refuses the information update and returns an error message to the application attempting the transaction.
The most common use of a trigger is to enforce business rules in the database. Triggers are used when the standard constraints or table-based Declarative Referential Integrity (DRI) are inadequate.
NOTE: Triggers run after the application of rules and other referential integrity checks. Therefore, if an operation fails these other checks, the trigger does not run. An operation must have otherwise succeeded before SQL Server will consider or execute a trigger's conditions or operations.
Triggers don't affect the server's performance significantly, and are often used to enhance applications that must perform many cascading operations on other tables and rows.
In SQL Server 6.x, Microsoft added ANSI-compliant DRI statements that you can use in the CREATE TABLE statement. The types of rules that these DRI statements can enforce are relatively complex. When these DRI statements are used in a CREATE TABLE statement, understanding exactly how SQL Server created the table can be quite difficult.
Besides the inability to perform complex business rule analysis based on values supplied when a trigger executes, DRI has one important limitation: The current implementation does not permit referencing values in other databases. Although this problem might seem relatively insignificant, it has a substantial impact on programmers attempting to write distributed applications that might need to check data constraints or values on other databases and servers.
When you create a trigger, you must be the database's owner. This requirement might seem odd at first, but if you consider what's happening, it really makes a lot of sense. When you add a trigger to a column, row, or table, you're changing how the table can be accessed, how other objects can relate to it, and so on. Therefore, you're actually changing the database schema. Of course, this type of operation is reserved for the database owner, protecting against someone inadvertently modifying your system's layout.
Creating a trigger is much like declaring a stored procedure, and it has a similar syntax:
CREATE TRIGGER [owner.]trigger_name ON [owner.]table_name FOR {INSERT, UPDATE, DELETE} [WITH ENCRYPTION] AS sql_statements
The following are options for the Transact-SQL command CREATE TRIGGER:
CAUTION: SQL Server uses the unencrypted text of a trigger stored in syscomments when a database is upgraded to a newer version. If the text is encrypted, you cannot update and restore the trigger to the new database. Make sure that the original text is available to upgrade the database when necessary.
NOTE: To provide a good level of recovery for your applications, you should always maintain an offline copy of your stored procedures, triggers, table definitions, and the overall structure of your SQL Server application's server side. You can use this information to reload the server if any problems occur.
NOTE: When a trigger executes, SQL Server creates a special table into which it places the data that caused the trigger to execute. The table is either inserted for insert and update operations or deleted for delete and update operations. Because triggers execute after an operation, the rows in the inserted table always duplicate one or more records in the trigger's base table. Make sure that a correct join identifies all the record's characteristics being affected in the trigger table so that the trigger does not accidentally modify the data itself.
SQL Server limits the types of SQL statements that you can execute while performing a trigger's actions. Most of these limitations derive from the fact that you cannot roll back the SQL, which you might need to do if the update, insert, or delete causing the trigger to execute in the first place is also rolled back.
The following is a list of Transact-SQL statements that you cannot use in a trigger's body text. SQL Server rejects the compilation and storing of a trigger with these statements:
NOTE: DELETE triggers do not execute when a TRUNCATE operation is initiated on a table. Because the TRUNCATE operation is not logged, the trigger has no chance to run. Only the table owner and the sa are permitted to perform a TRUNCATE, and this permission cannot be transferred.
Additionally, you need to understand the following limitations clearly:
This section presents examples of the creation of several types of triggers. These examples aren't very sophisticated but should give you ideas on how you might implement triggers in your own environment.
Triggers fire or execute whenever a particular event occurs. The following subsections demonstrate the different events that can cause a trigger to execute and should give you some ideas of what you might have your trigger do when such events occur.
INSERT and UPDATE triggers are particularly useful because they can enforce referential integrity constraints and ensure that your data is valid before it enters the table. Typically, INSERT and UPDATE triggers are used to update timestamp columns or to verify that the data on the columns that the trigger is monitoring meets the criteria required. Use INSERT and UPDATE triggers when the criteria for verification are more complex than a declarative referential integrity constraint can represent.
In Listing 15.1, the trigger executes whenever a record is modified or inserted into the SALES table. If the order date is not during the first 15 days of the month, the record is rejected.
Create Trigger Tri_Ins_Sales On SALES For INSERT, UPDATE As /* declare local variables needed */ Declare @nDayOfMonth tinyint /* Find the information about the record inserted */ Select @nDayOfMonth = DatePart( Day, I.ORD_DATE ) From SALES S, INSERTED I Where S.STOR_ID = I.STOR_ID And S.ORD_NUM = I.ORD_NUM And S.TITLE_ID = I.TITLE_ID /* Now test rejection criteria and return an error if necessary */ If @nDayOfMonth > 15 Begin /* Note: Always Rollback first. You can never be sure what kind of error processing a client may do that may force locks to be held for unnecessary amounts of time */ ROLLBACK TRAN RAISERROR ( `Orders must be placed before the 15th of the month', 16, 10 ) End Go
NOTE: Notice how the previous join refers to the inserted table. SQL Server specially creates this logical table to enable you to reference information in the record that you are modifying. By using the alias I as shown, you can easily reference the table in the join criteria specified in the Where clause.
Notice that the code segment references a new table. If you review the list of tables, you'll notice that the database doesn't include the table. In this case, the inserted table contains a copy of every row that would be added if the transaction is allowed to complete. You use the inserted table's values to feed the information to any comparisons that you want to make to validate the transaction.
The columns in the inserted table exactly match those in the table with which you're working. You can perform comparisons on the columns, as in the example, which compares the columns against the sales database to verify that the sales date is valid.
You can also create triggers that can do their work only if a given column is updated. You can use the If Update statement in your trigger to determine whether the trigger processing should continue:
if update(au_lname) and (@@rowcount=1) begin ... end
In this case, the only time that the code within the segment executes is if the specific column, au_lname, is updated. Keep in mind that although a column is being updated, it isn't necessarily being changed. Many applications, including most proprietary systems, simply update the entire record if any change is made.
Before taking further action in the trigger, you might find it helpful to compare the new value against the old value (with the inserted table stores) to see whether the value has indeed changed.
DELETE triggers are typically used for two reasons. The first reason is to prevent deletion of records that will cause data integrity problems if they indeed are deleted. An example of such records are those used as foreign keys to other tables.
The second reason for using a DELETE trigger is to perform a cascading delete operation that deletes children records of a master record. You might use such a trigger to delete all the order items from a master sales record.
TIP: When you create a trigger, remember that it can affect more than one row. You must consider this possibility in any procedure that the trigger runs. Be sure you check the @@rowcount global variable to see exactly what is happening before you begin working with the information.Triggers take into account the sum total of all rows that the requested operation affects, so they must be capable of considering the different combinations of information in the table and respond according to what you need. For example, if you issue a DELETE * from Authors statement, the trigger must accommodate the fact that the statement will delete all records from the AUTHORS table.
In the example in Listing 15.2, the @@rowcount variable prevents the deletion of more than one row at a time.
In Listing 15.2, the trigger executes whenever a user attempts to delete a record from the STORES table. If the store has sales, the trigger denies the request.
Create Trigger Tri_Del_Stores On STORES For DELETE As /* First check the number of rows modified and disallow anybody from deleting more than one store at a time */ If @@RowCount > 1 Begin ROLLBACK TRAN RAISERROR ( `You can delete only one store at a time.', 16, 10 ) End /* declare a temp var to store the store that is being deleted */ Declare @sStorID char(4) /* now get the value of the store being nuked */ Select @sStorID = D.STOR_ID From STORES S, DELETED D Where S.STOR_ID = D.STOR_ID If exists (Select * From SALES Where STOR_ID = @sStorID ) Begin ROLLBACK TRAN RAISERROR ( `This store cannot be deleted because there are still sales valid in the SALES table.', 16, 10 ) End Go
TIP: Using RAISERROR is an easy way to send the calling process or user detailed, specific information about the error to the calling process or user. RAISERROR enables you to specify error text, severity levels, and state information, all of which combine for more descriptive error messages for the user. RAISERROR also makes it easy to write generic error handlers in your client applications.
Listing 15.2 shows several transaction-management statements that enable you to stop the operation. For more information about transactions, see Chapter 12, "Understanding Transactions and Locking."
Notice that the code segment references a new table. If you review the list of tables present, you'll notice that the database doesn't list this new table. In this case, the deleted table contains a copy of every row that would be deleted if the transaction were allowed to complete. You use the deleted table's values to feed the information to any comparisons that you want to make to validate the transaction.
The columns in the deleted table exactly match those in the table with which you're working. You can perform comparisons on them, as in Listing 15.2, in which they're compared against the sales database to verify that the store has no sales outstanding.
If you are working with triggers and transactions, you might want to consider working with a special trigger option, the rollback trigger:
Rollback trigger [with raiserror errornumber [message]]
The rollback trigger option is, in essence, an abort-all statement. When a rollback is encountered, the trigger's processing stops and the data modification that caused the trigger to execute in the first place is not disallowed.
When you use the rollback trigger statement, you have the option--even the responsibility--to indicate an error number and optional message. Except in very rare situations, you should use the RAISERROR option, because it tells the calling routines that you have stopped the action from occurring. The rollback trigger statement doesn't stop processing for a batch of updates; instead, the trigger fails only the current item. Therefore, the code that you develop to update the database must check the return state of the update to ensure that it succeeded.
When the routine returns from the update operation, always check the @@error global variable to ensure that the updates happened as planned.
One of the better features of SQL Server is its capability to invoke behavior directly from the operating system. You must predefine such behavior through SQL Server's extended procedures, but they enable you to create incredibly powerful trigger operations. SQL Server is relatively unique in its capability to support features specific to the operating system. SQL Server can offer this support because it runs only on Windows NT, which has a very stan-dardized programming interface across all its supported hardware platforms, such as Intel, MIPS, Alpha, and PowerPC.
Triggers can call any of the extended procedures (xp_*) available to the server and any external procedures that you add to the server with the sp_addextendedproc command. In Listing 15.3, the trigger demonstrates how to send e-mail when a record is deleted from the underlying AUTHORS table.
Create Trigger Tri_Del_Authors_Mail On AUTHORS For DELETE As /* declare some variables to store the author's name */ Declare @sLName varchar(40), @sFName varchar(20), @sAuthor varchar(60) /* now get the value of the author being removed */ Select @sLName = D.AU_LNAME, @sFName = D.AU_FNAME From AUTHORS A, DELETED D Where A.AU_ID = D.AU_ID /* Send mail message */ Select @sAuthor = @sLName + `, ` + @sFName exec master.dbo.xp_sendmail @recipient = `ChiefPublisher', @message = `deleted ` + @sAuthor Go
You can nest triggers up to 16 layers deep. If nested trigger operations are not desirable, however, you can configure SQL Server to disallow them. To toggle this option, use the nested trigger option sp_configure.
See "Displaying and Setting Server Options," Chapter 16.
Triggers become nested when the execution of one trigger modifies another table that includes another trigger, which therefore executes.
TIP: You can check your nesting level at any time by inspecting the value in @@NestLevel. The value is between zero (0) and 16.
SQL Server cannot detect nesting that causes an infinite loop during the creation of a trigger until the situation occurs at execution time. A trigger can cause an infinite loop. For example, suppose that TABLE_A includes TRIGGER_A, which executes when TABLE_A is updated. When executed, TRIGGER_A causes an update on TABLE_B. TABLE_B has a similar trigger, TRIGGER_B, that executes when TABLE_B is updated and causes an update of TABLE_A. Thus, if a user updates either table, the two triggers continue executing each other indefinitely. On detecting such an occurrence, SQL Server shuts down or cancels the trigger.
NOTE: If a trigger causes an additional modification of the table from which it executes, the trigger does not cause itself to execute recursively. The current version of SQL Server has no support for reentrant or recursive stored procedures or triggers.
As another example, suppose that the SALES table includes one trigger, and the STORES table includes another. Listing 15.4 defines both triggers.
/* First trigger deletes stores if the sales are deleted */ Create Trigger Tri_Del_Sales On SALES For DELETE As /* Announce the trigger being executed */ Print "Delete trigger on the sales table is executing..." /* declare a temp var to store the store that is being deleted */ Declare @sStorID char(4), @sMsg varchar(40) /* now get the value of the store being deleted */ Select @sStorID = STOR_ID From DELETED /* DELETED is a fake table created by SQL Server to hold the values of records deleted */ Group By STOR_ID /* Now delete the store record */ Select @sMsg = "Deleting store " + @sStorID Print @sMsg Delete STORES Where STOR_ID = @sStorID Go /* Second trigger deletes discounts if a store is deleted */ Create Trigger Tri_Del_Stores On STORES For DELETE As /* Announce the trigger being executed */ Print "Delete trigger on the Stores table is executing..." /* Declare a temp var to store the store that is being deleted */ Declare @sStorID char(4), @sMsg varchar(40) /* now get the value of the store being deleted */ Select @sStorID = sTOR_ID From DELETED /* DELETED is a fake table created by SQL Server to hold the values of records deleted */ Group By STOR_ID If @@rowcount = 0 Begin Print "No rows affected on the stores table" Return End /* Now delete the store record */ Select @sMsg = "Deleting discounts for store " + @sStorID Print @sMsg Delete DISCOUNTS Where STOR_ID = @sStorID Go
If a DELETE executes on the SALES table, as shown in Listing 15.5, the trigger executes on the SALES table, which in turn causes a trigger to execute on the STORES table.
/*---------------------------- Delete from sales where stor_id = `8042' ----------------------------*/ Delete trigger on the sales table is executing... Deleting store 8042 Delete trigger on the Stores table is executing... Deleting discounts for store 8042
TIP: Triggers and DRI usually don't work well together. For example, in Listing 15.5, you must first drop the Foreign Key constraint on the DISCOUNTS table before it can actually complete the DELETE. Wherever possible, you should implement either triggers or DRI for integrity constraints.
If you want to view the behavior that a trigger is enforcing on a table, you must display the information that describes any triggers that the table owns. There are several ways to obtain information about a trigger on any given table. This section demonstrates the two most common ways: SQL Enterprise Manager (SQL EM) and the system procedures sp_help and sp_depends.
To use the SQL Enterprise Manager to view information about a trigger, perform the following steps:
FIG. 15.1
After highlighting a table, you can right-click and use the quick menu to perform
common operations that are also available from the Manage menu.
FIG. 15.2
In the SQL Enterprise Manager's Manage Triggers dialog box, the toolbar's second
combo box lists the triggers that are active on the table in the first combo box.
The system procedures sp_help, sp_depends, and sp_helptext provide valuable information for determining whether a trigger exists, what it references, and what its actual text or source code looks like. (These procedures work only if you did not use the ENCRYPTION option when creating the trigger.)
Using sp_help sp_help is a generic system procedure that reports information about any object in the database. The procedure requires the following syntax:
sp_help [object_name]
If you omit object_name, SQL Server reports information on all user objects found in the sysobjects system catalog table.
sp_help is useful for determining who created a trigger and when he or she created it. Listing 15.6 is an example of the output from sp_help when used on the trigger Tri_Del_Authors.
/*---------------------------- sp_help Tri_Del_Authors ----------------------------*/ Name Owner Type When_created ------------------ ------------ ------------------ -------------- Tri_Del_Authors dbo trigger Nov 26 1995 4:37PM Data_located_on_segment ---------------------- not applicable
Listing 15.7 shows a more advanced trigger that will be used in the following sections.
/* create a basic trigger to stop anyone deleting an author that still has records titleauthor table */ Create Trigger Tri_Del_Authors On AUTHORS For DELETE As /* First check the number of rows modified and disallow anybody from removing more than one author at a time */ If @@RowCount > 1 Begin ROLLBACK TRAN RAISERROR ( `You can delete only one author at a time.', 16, 10 ) End /* declare a temp var to store the author that is being deleted */ Declare @nAuID id /* now get the value of the author being deleted */ Select @nAuID = D.AU_ID From AUTHORS A, DELETED D /* DELETED is a fake table created by SQL Server to hold the values of records deleted */ Where A.AU_ID = D.AU_ID If exists (Select * From TITLEAUTHOR Where AU_ID = @nAuID ) Begin ROLLBACK TRAN RAISERROR ( `This author cannot be deleted because he/ she still has valid titles.', 16, 10 ) End
Go
Using sp_depends sp_depends is a useful system-stored procedure that returns a database object's dependencies, such as tables, views, and stored procedures. The syntax is as follows:
sp_depends object_name
After you add the trigger shown in Listing 15.7, sp_depends produces the output shown in Listing 15.8 when run on the AUTHORS table.
/*---------------------------- sp_depends authors ----------------------------*/ In the current database, the specified object is referenced by the following: name type ---------------------------------------- ---------------- dbo.reptq2 stored procedure dbo.titleview view dbo.Tri_Del_Authors trigger
Using sp_helptext User-defined objects--such as rules, defaults, views, stored procedures, and triggers--store their text in the system catalog table syscomments. This table is difficult to read, but you can use the sp_helptext procedure to make the table more accessible. The syntax for sp_helptext is as follows:
sp_helptext object_name
If you cannot read the text returned, the trigger was probably stored as ENCRYPTED. In this format, you cannot read the trigger's associated commands. You'll have to contact the trigger's original author and request the procedure's text file.
For several reasons, you might want to remove triggers from a table or tables. You might, for example, be moving into a production environment and want to remove any triggers that you put in place to ensure good quality but which were hurting performance. You might also want to drop a trigger simply to replace it with a newer version.
To drop a trigger, use the following syntax:
DROP TRIGGER [owner.]trigger_name[,[owner.]trigger_name...]
Dropping a trigger is not necessary if a new trigger is to be created to replace the existing one. When you drop a table, you also drop all its child-related objects, including triggers.
The following example drops the trigger created for Tri_Del_Authors:
Drop Trigger Tri_Del_Authors
Triggers and referential integrity in general present some interesting challenges when you create the SQL Server application from scratch, never mind the times when you're upsizing an application from a different platform. As you'll see in Chapter 26, "Upsizing Microsoft Office 97 Applications to SQL Server," there are several ways you can move your tables and queries to SQL Server. From that point, however, you'll need to know and understand triggers if you're to maintain the referential integrity that you've implemented in Access on the SQL Server side of things.
Perhaps one of the most frequently created triggers is the trigger that controls a deletion on a given record. A major component of many referential integrity scenarios is that of preventing the removal of a record if child, or related, records exist. While simple to implement (see earlier in this chapter for specific examples), be sure to include it on your list of items to check after a conversion.
Referential integrity is a bit of a ghost-feature. You don't see it until you either run into it by breaking a rule, or by looking specifically to see what's in force. It's much less evident than typical objects like tables, stored procedures, and so on. Make sure you use sp_help against each table you convert. This will save you the aggravation of finding out you had a trigger that was unknown to you on the original table.
In this chapter, you learned about the values of triggers and how you can apply them to enforce referential integrity in your application. In addition, you learned that you can nest triggers and use them to provide more complex business rule validation than constraints that you can define when creating tables.
For more information that might be useful in helping you write effective triggers, see the following chapters:
© Copyright, Macmillan Computer Publishing. All rights reserved.