Lesson 3:Programming Triggers

3 4

---

After this lesson, you will be able to:

• Create triggers to apply business logic to the database.
• Determine how to use the Inserted and Deleted pseudo tables.
• Use common trigger system functions.

Estimated Lesson time: 40 minutes

---

The Inserted and Deleted Pseudo Tables

When an INSERT, UPDATE, or DELETE trigger fires, the event creates one or more pseudo tables (also known as logical tables). These logical tables can be thought of as the transaction logs of the event. There are two types of logical tables: the Inserted table and the Deleted table. An insert or update event creates an Inserted logical table. The Inserted logical table contains the record set that has been added or changed. The UPDATE trigger also creates a Deleted logical table. The Deleted logical table contains the original record set as it appeared before the update. The following example creates a trigger that displays the contents of the Inserted and Deleted tables following an update event to the Authors table:

 CREATE TRIGGER dbo.updatetables ON dbo.authors AFTER UPDATE AS SELECT "Description" = 'The Inserted table:' SELECT * FROM inserted SELECT "Description" = 'The Deleted table:' SELECT * FROM deleted 

Following a simple UPDATE statement that changes an author's name from Dean to Denby, the trigger displays the following results:

 The Inserted table: Straight  Denby  Oakland  CA  94609 The Deleted table: Straight  Dean  Oakland  CA  94609 

The Authors table (trigger table) contains the updated record after the update trigger runs. When the trigger fires, the update to the Authors table can be rolled back by programming logic into the trigger. This transaction rollback capability also applies to INSERT and DELETE triggers.

When a delete trigger fires, the Deleted logical table contains the deleted record set. The Inserted table is not part of a DELETE event.

NOTE

---

The SELECT statement appears in the previous code example for illustrative purposes only. Never use statements in triggers that return a result unless you are sure that the result returned by the trigger can be handled by any applications that cause the trigger to fire.

Trigger Syntax, System Commands, and Functions

Now that you understand the purpose of triggers and the various types of triggers, the next step is to investigate the syntax for creating and altering a trigger. Functions and system commands augment triggers by allowing you to implement business logic in the trigger.

UPDATE (column_name) and (COLUMNS_UPDATED()) Clauses

Two important clauses that are part of the CREATE TRIGGER and ALTER TRIGGER statements are UPDATE (column_name) and (COLUMNS_UPDATED()). These two clauses can be part of an Insert or Update trigger and can appear anywhere in the CREATE TRIGGER or ALTER TRIGGER statement.

The IF UPDATE (column_name) clause determines whether an INSERT or UPDATE event occurred to the column named column_name. If you need to specify more than one column, separate each column name with UPDATE (column_name). For example, the following code segment checks to determine whether the First_Name and Last_Name columns were inserted or updated and does something as a result of an INSERT or UPDATE to either of these columns:

 IF UPDATE (first_name) OR UPDATE (Last_Name)  BEGIN      Do some conditional processing when either of these columns are     
     updated.  END 

Because of deferred name resolution, the column specified by column_name does not need to exist when the trigger is applied to the table. However, the column must exist when the trigger fires. For more information about deferred name resolution, refer to Chapter 8, "Implementing Stored Procedures."

If the column_name is replaced with a value, the UPDATE clause returns TRUE. The (COLUMNS_UPDATED()) clause also tests for updated columns. Instead of returning true or false, (COLUMNS_UPDATED()) returns a varbinary bit pattern that indicates which of the tested columns were inserted or updated.

The (COLUMNS_UPDATED()) clause is more complex to write than the UPDATE (column_name) clause, but it returns exactly which tested columns were inserted or updated. You specify which columns to test by using a bit mask to represent each column (ordinal) position in the table. The following table shows the first eight columns and the bit mask assigned to each column:

Column	1	2	3	4	5	6	7	8
Bit Mask	1	2	4	8	16	32	64	128

To test whether columns 4 or 6 were updated, you can use the following code:

 IF (COLUMNS_UPDATED() & 40) > 0 

The value of 40 is derived from summing 8 for column 4 and 32 for column 6. The (COLUMNS_UPDATED()) expression tests whether the return value is greater than 0. In other words, if either or both of the columns are updated, the columns updated condition is met. If you set (COLUMNS_UPDATED() & 40) = 40, then you are testing whether both of the columns are updated. Updates to one or the other column do not meet the columns updated condition.

To test more than eight columns, you must use the SUBSTRING function so that the trigger knows which bit mask to test. For example, to test for an update to the ninth column, you can use the following code:

 IF ((SUBSTRING(COLUMNS_UPDATED(),2,1)=1)) 

The SUBSTRING function tells the (COLUMNS_UPDATED()) clause to move to the second octet of columns and then test for an update to column 1 of the second octet (which is actually in ordinal position 9). The varbinary return value for this column is 1. The following table shows the SUBSTRING function necessary to test columns 9 through 16:

 IF ((SUBSTRING(COLUMNS_UPDATED(),2,y)=z)) 

Column	9	10	11	12	13	14	15	16
y and z	1	2	4	8	16	32	64	128

To test modifications to multiple columns, simply add the bit values for each column. For example, to test columns 14 and 16, specify 160 (32 + 128) for the z value.

Functions and System Commands

Numerous functions and system commands are used in triggers to implement business logic. A function commonly used in triggers is @@ROWCOUNT. This function returns the number of rows affected by the previous Transact-SQL statement in the trigger. If no rows are affected by an INSERT, UPDATE, or DELETE event, the trigger still fires. Therefore, use the RETURN system command to exit the trigger transparently when no table modifications are made.

In the event of an error, you might want to display a message describing the reason for the error. The RAISERROR system command is used to display error messages. You can create custom error messages with the sp_addmessage system stored procedure, or you can display ad hoc error messages when calling the RAISERROR system command. Refer to SQL Server Books Online for more information about the sp_addmessage system stored procedure.

The ROLLBACK TRANSACTION system command might also appear in the Transact-SQL code for the trigger. This command explicitly rolls back the entire batch of the trigger. A fatal error also causes an implicit rollback. You don't have to specify ROLLBACK TRANSACTION in the trigger code if your goal is to complete the transaction in all cases except if a fatal error occurs during the transaction.

Transact-SQL Language Precautions

You can use SELECT and PRINT statements or assign variables in the trigger's code, but using them to return result sets, messages, or values is dangerous. When an event causes a trigger to fire, the trigger is usually transparent to the user or application. If the application isn't designed to handle return code, such as a result set from a SELECT statement, then the application might fail. Using a SELECT statement to provide a value to a conditional statement is perfectly acceptable. For example, you might want to use a SELECT statement to test for the existence of a value and return that value to the IF EXISTS statement so that additional processing is performed. If you need to assign variables in a trigger's code, include SET NOCOUNT ON in the trigger code.

The following Transact-SQL statements are not allowed in a trigger:

• ALTER, CREATE, DROP, RESTORE, and LOAD DATABASE
• LOAD and RESTORE LOG
• DISK RESIZE and DISK INIT
• RECONFIGURE

Common Trigger Programming Tasks

Triggers are used to execute all types of business logic before (INSTEAD OF) or after (AFTER) an INSERT, UPDATE, or DELETE statement is run against a table or a view. Triggers are written for common tasks such as the following:

• Maintaining running totals and other computed valuesDatabases constantly change as data is added, deleted, and modified in tables. In some cases, a column value in one table is computed from data modifications in another table. Triggers are ideal for maintaining computed columns. Figure 9.3 shows a trigger used to update the retail price of an item in the Inventory table when the average wholesale price changes in the Purchasing table.

Figure 9.3  The Inventory and Purchasing tables.

The following trigger is applied to the Purchasing table. It calculates the average wholesale cost of inventory and increases the price by 30 percent, in addition to updating the retail_price column of the inventory table with the new value:

 CREATE TRIGGER Retail_Price_Update ON purchasing AFTER INSERT, UPDATE, DELETE AS SET NOCOUNT ON IF EXISTS (SELECT item_num FROM inserted) BEGIN  UPDATE inventory   SET retail_price =  (SELECT (SUM(pur.total_cost)/SUM(pur.qty_ordered) * 1.30)   FROM purchasing pur INNER JOIN inserted ins    ON pur.item_num = ins.item_num)  FROM inventory inv, inserted ins  WHERE inv.item_num = ins.item_num END ELSE IF EXISTS (SELECT item_num from deleted) BEGIN  UPDATE inventory   SET retail_price =  (SELECT (SUM(pur.total_cost)/SUM(pur.qty_ordered) * 1.30)   FROM purchasing pur INNER JOIN deleted del    ON pur.item_num = del.item_num)  FROM inventory inv, deleted del  WHERE inv.item_num=del.item_num  END ELSE  BEGIN   RAISERROR ('The retail price has not been adjusted for the product.', 16, 1)   RETURN END 

The trigger uses the SET NOCOUNT ON statement so that when the trigger fires and updates information in the Inventory table, the SELECT statement result is not displayed. Conditional processing checks for the existence of the Inserted or Deleted tables. If neither the Inserted nor Deleted tables contain records, the trigger uses RAISERROR to display an ad hoc message that no prices were adjusted. You can use sp_addmessage to add a custom message to the SysMessages system table and then specify the message number in place of the custom message.

• Creating audit records

For security reasons or to simply track activity on a table (or tables) in the database, you can create a trigger to update a table with data inserted, updated, or deleted from another table or view.

• Invoking external actions

A trigger can specify an action outside of standard database processing to occur when it fires. For example, in Lesson 2 you saw an example of using the xp_sendmail extended stored procedure in a trigger to send a message to an e-mail account when the trigger fires.

• Implementing complex data integrity

Sometimes standard data integrity measures are not enough. For example, the cascading delete action deletes records in other tables when a delete action would violate referential integrity between the tables. However, you might not want to allow a cascading delete action to occur. You could use an INSTEAD OF trigger to delete records from related tables but then log the deleted result to another table for later review. If you use an INSTEAD OF trigger to delete records, you cannot implement a delete action by using cascading referential integrity. To accomplish the same task, you must program the cascading delete action in the trigger.

Exercise 3: Creating a Trigger to Update a Column Value

In this exercise, you will create and test a trigger that updates the value to 1 in the Sold column of the Books table in BookShopDB. You will add conditional processing so that if the book is returned, the Sold column is updated with a value of 0.

To create an INSERT trigger to apply to the BookOrders table

1. Open Query Analyzer and connect to your local server.
2. In the Editor pane of the Query window, enter and execute the following code:

 CREATE TRIGGER dbo.update_book_status  ON dbo.bookorders  AFTER INSERT AS  UPDATE books   SET Sold = 1    WHERE titleid =    (SELECT bo.titleid      FROM bookorders bo INNER JOIN inserted i      ON bo.orderid = i.orderid) 

The CREATE TRIGGER statement creates a trigger named Update_Book_Status and applies the trigger to the BookOrders table of the BookShopDB database. When an insert to the BookOrders table occurs, this trigger fires and updates the Sold column for the matching TitleID value of the Books table.

To alter the trigger to accommodate BookOrders table deletions and updates

1. In the Editor pane of the Query window, enter and execute the following code:

 ALTER TRIGGER dbo.update_book_status  ON dbo.bookorders  AFTER INSERT, DELETE AS  SET NOCOUNT ON  IF EXISTS (SELECT * FROM inserted)  BEGIN   UPDATE books    SET Sold = 1     WHERE titleid =     (SELECT bo.titleid       FROM bookorders bo INNER JOIN inserted i       ON bo.orderid = i.orderid)  END  ELSE  BEGIN   UPDATE books    SET Sold = 0     WHERE titleid =     (SELECT d.titleid       FROM books b INNER JOIN deleted d       ON b.titleid = d.titleid)  END 

The ALTER TRIGGER statement modifies the trigger named Update_Book_Status and applies the trigger to the BookOrders table of the BookShopDB database. The DELETE event appears after the INSERT statement in the third line of code. When a delete in the BookOrders table occurs, this trigger fires and updates the Sold column to 0 for the matching TitleID. This business logic is important to accommodate books that are returned. Notice that the SELECT statement for the delete condition (after the ELSE clause) matches the TitleID in the books table and the TitleID in the Deleted table. This modification is necessary because the BookOrders table no longer contains the deleted record.

2. The previous code does not handle updates. The only update that is relevant to the Books table is an update to the TitleID column of the BookOrders table. If the OrderID is changed in the BookOrders table, the book should still have a Sold value of 1. You must be able to handle updates to the TitleID value. In the Editor pane of the Query window, enter and execute the following code:

 ALTER TRIGGER dbo.update_book_status  ON dbo.bookorders  AFTER INSERT, UPDATE, DELETE AS  SET NOCOUNT ON  IF EXISTS (SELECT * FROM inserted)  BEGIN   UPDATE books    SET Sold = 1     WHERE titleid =     (SELECT bo.titleid       FROM bookorders bo INNER JOIN inserted i       ON bo.orderid = i.orderid)  END  IF EXISTS (SELECT * FROM deleted)  BEGIN   UPDATE books    SET Sold = 0     WHERE titleid =     (SELECT d.titleid       FROM books b INNER JOIN deleted d       ON b.titleid = d.titleid)  END 

The trigger is modified to include UPDATES. Notice that the ELSE clause has been removed. An UPDATE event always creates both the Inserted and Deleted tables. Therefore, the first part of the code sets the Sold status to 1 for the new TitleID value. The second part of the code detects the Deleted table and sets the Sold status for the original TitleID to 0.

To test the trigger

1. In the Editor pane of the Query window, enter and execute the following code:

 SET IDENTITY_INSERT orders ON INSERT INTO orders (orderid, customerid, employeeid, amount, orderdate, deliverydate, paymentid, statusid) VALUES (20, 10, 1, 500, GETDATE(), GETDATE() + 5, 2, 1) SET IDENTITY_INSERT orders OFF GO 

An order is inserted into the Orders table.

2. In the Editor pane of the Query window, enter and execute the following code:

 INSERT INTO bookorders (orderid, titleid) values (20, 'carr9675') 

A book order for CustomerID 20 for TitleID carr9675 is inserted into the BookOrders table.

3. In the Editor pane of the Query window, enter and execute the following code:

 SELECT * from Books where titleid = 'carr9675' 

This command verifies that the Sold status for this book has changed to 1.

4. In the Editor pane of the Query window, enter and execute the following code:

 UPDATE bookorders set titleID = 'aust1234' where titleid = 'carr9675' 

5. In the Editor pane of the Query window, enter and execute the following code:

 SELECT * from Books WHERE titleid = 'carr9675' OR titleid = 'aust1234' 

This command verifies that the Sold status for the original TitleID is 0 and that the new TitleID is 1.

6. In the Editor pane of the Query window, enter and execute the following code:

 DELETE bookorders WHERE titleid = 'aust1234' 

This command removes the book order from the BookOrders table, and the trigger changes the Sold status back to 0.

7. In the Editor pane of the Query window, enter and execute the following code:

 SELECT * from Books WHERE titleid = 'aust1234' 

8. In the Editor pane of the Query window, enter and execute the following code:

 DROP TRIGGER dbo.update_book_status 

This command removes the trigger from the BookOrders table.

Lesson Summary

In this lesson, you learned how to program triggers. Triggers create two logical tables when an INSERT, UPDATE, or DELETE event occurs in a table to which a trigger is applied. These tables enable you to determine exactly what data modifications were made to a table when the trigger fired. Detecting data modifications is an essential step toward programming business logic into triggers. CREATE and ALTER TRIGGER syntaxes include two useful clauses for detecting column inserts or updates: UPDATE (column_name) and (COLUMNS_UPDATED()). The UPDATE (column_name) clause enables you to take an action based on modifications occurring to a specific column. The (COLUMNS_UPDATED()) clause enables you to detect multiple changes in columns designated by a varbinary bit mask value. The first eight columns are represented by a bit mask ranging from 1 for column 1 to 128 for column 8. Column changes beyond column eight can be detected using the SUBSTRING() function.

Other functions exist besides SUBSTRING() and system commands that are commonly used in triggers. The @@ROWCOUNT function returns the number of rows affected by a table modification. The RAISERROR system command displays either custom messages or messages contained in the SysMessages system table. Another important system command used in triggers is ROLLBACK TRANSACTION. This command enables you to control events that cause the UPDATE, INSERT, or DELETE event to be backed out. Use SET NOCOUNT ON in a trigger to prevent it from returning the rows affected message to the calling application. Triggers are often used to maintain running totals or other computed values across tables, to create audit records to track table data modifications, to invoke external actions, and to implement complex data integrity features.