Lesson 1:Introduction to Triggers | MCSE Training Kit (Exam 70-229): Microsoft SQL Server(TM) 2000 Database Design and Implementation (Pro Certification)

3 4

Implementing data integrity features is critical for maintaining the accuracy and consistency of the database. Complex procedural data integrity methods and business logic can be added to a database using triggers, a special type of stored procedure that is applied to tables and views. Data modification events cause triggers to fire automatically. In this lesson, you will learn when to use triggers and when to apply other less resource-intensive data integrity measures, such as cascading referential integrity. You will also learn about the various types and classes of triggers and their features and limitations.

After this lesson, you will be able to:

Explain how to use trigger types (INSERT, UPDATE, and DELETE triggers) and trigger classes (INSTEAD OF and AFTER triggers) to extend data integrity.
Apply cascading referential integrity in place of triggers.
Describe trigger features and limitations.

Estimated Lesson time: 30 minutes

Extending Data Integrity with Triggers

The quality of a database is measured partly by the consistency and accuracy of the data in the database. Declarative data integrity and procedural data integrity are used to maintain data consistency and accuracy. Chapter 5, "Implementing Data Integrity," explored declarative data integrity methods. This chapter explores a common procedural data integrity method, triggers.

Triggers enable you to write a procedure that is invoked when table data is modified with an INSERT, UPDATE, or DELETE action. A trigger is applied to either a table or a view. Triggers are used to enforce business rules in the database. For example, one business rule identified for the BookShopDB database is as follows: When a book is added to an order, it is marked as sold in the inventory. A trigger applied to the BookOrders table can fire when a book order is inserted. The business logic in the trigger locates the book in the Books table and flags the book as sold. You will create this trigger in Exercise 3.

Procedural Data Integrity

Before choosing to implement a trigger, consider whether the same results can be achieved by using constraints or rules. Use entity integrity to uniquely identify rows of table data (primary key and unique key constraints). Use domain integrity to define default values (default constraints) and to restrict column data to acceptable values (check and referential constraints). Use referential integrity to enforce logical relationships between tables (foreign key and check constraints). Use default constraints to provide a value for a required field when the field is not specified in an INSERT statement. Use constraints before choosing to use triggers and rules. Rules remain in SQL Server 2000 for backward compatibility, and the overuse of triggers can degrade system performance. The query optimizer uses constraint definitions to build high-performance query-execution plans. Chapter 5 discusses all data integrity methods (except for triggers). Triggers are especially useful because they can contain significantly more complex processing logic than is possible with declarative integrity methods. Use triggers in the following instances:

If using declarative data integrity methods does not meet the functional needs of the application. For example, create a trigger that changes a numeric value in a table when a record in the same table is removed.
Changes must cascade through related tables in the database. For example, create and apply a trigger to an Orders table so that when an order is placed, stock quantity in an inventory table is changed. Create and apply another trigger to the Inventory table so that when the quantity changes, a purchase request is added to the Purchasing table.
Use cascading referential integrity constraints instead of a custom trigger if your goal is to update or delete a foreign key constraint.
If the database is denormalized and requires an automated way to update redundant data contained in multiple tables
If a value in one table must be validated against a non-identical value in another table
If customized messages and complex error handling are required

Trigger Features and Limitations

Cascading referential integrity extends the accuracy and consistency of data by applying updates or deletions to foreign keys within the database. Triggers go further by extending data integrity to any table column in the database or even to objects outside the current database. You can also apply triggers to views.

A single trigger can run multiple actions, and it can be fired by more than one event. For example, you can create a single trigger that runs when any valid event, INSERT, UPDATE, or DELETE occurs. Within the Transact-SQL code, you can define business logic to handle each type of event.

Triggers cannot be created on a temporary or system table, although the Transact-SQL language within the trigger can reference temporary tables or system tables. An important limitation to be aware of is that INSTEAD OF DELETE and INSTEAD OF UPDATE triggers cannot be defined on tables that have corresponding ON DELETE or ON UPDATE cascading referential integrity defined. You will learn more about INSTEAD OF triggers later in this lesson.

Trigger Events

Three events automatically fire a trigger: INSERT, UPDATE, and DELETE events that occur on a table or on a view. Triggers cannot be fired manually. The trigger syntax always includes defining one or more of these events before the task-specific Transact-SQL language is specified. Trigger types correspond to the event. For example, you can create an update trigger so that when an update occurs to a table, the update trigger is fired. A single trigger can be assigned multiple events so that you could have a procedure that is both an update and an insert trigger. The events can be listed in any order within the trigger definition. You will learn more about trigger syntax in Lesson 2.

There are certain instances when an event that modifies or deletes data does not fire a corresponding trigger. For example, the TRUNCATE TABLE statement does not fire triggers defined for DELETE events. An important feature of triggers is that unsuccessful transactions are automatically rolled back. Because TRUNCATE TABLE is not a logged event, it cannot be rolled back so it doesn't fire the DELETE trigger. Additionally, the WRITETEXT statement does not cause the INSERT or UPDATE triggers to fire.

Trigger Execution

When a table insert or update fires a trigger, the trigger stores the new or modified data in a table named Inserted. When a table delete fires a trigger, the trigger stores the deleted data in a table named Deleted. The table exists in memory and is queried within the trigger by using Transact-SQL commands. This capability is critical to the function of most triggers because the task within the trigger, such as modifying a value in an associated table, compares the data contained in the Inserted or Deleted tables to the data in the modified table before the changes are committed. By using the information stored in the Inserted or Deleted table, a trigger can roll back a transaction to enforce business rules.

There are two classes of triggers in SQL Server 2000: INSTEAD OF and AFTER. INSTEAD OF triggers bypass the triggering action and run in their place. For example, an update to a table containing an INSTEAD OF trigger will run the trigger Transact-SQL code rather than the update statement. This feature enables you to specify complex processing statements within the trigger to augment the table-modification statement. AFTER triggers fire as a supplement to the triggering action and are the default trigger class. A trigger can be applied to only a single table or view.

There are a number of important differences between these trigger classes, as shown in the following table:


Characteristic	INSTEAD OF	AFTER
Applied to	Defined on a table or a view	Defined on a table
	Defining a trigger on a view extends the types of updates that a view can support.	Modifications to views will fire AFTER triggers when table data is modified in response to the view modification.
Number allowed	Only one per triggering action is allowed on a table or view.	More than one allowed on a table
	You can define views of other views where each view has its own INSTEAD OF trigger.
Order of execution	Only one per triggering action is allowed on a table or view, so the order is irrelevant.	Can define which trigger fires first and last Use the sp_settriggerorder system stored procedure to define the first and last trigger. All other trigers applied to a table execute in random orderand last trigger.

You can apply both classes of triggers to a table. If you have defined both trigger classes and constraints for a table, the INSTEAD OF trigger fires. Then, constraints are processed and AFTER triggers fire. If constraints are violated,INSTEAD OF trigger actions are rolled back. AFTER triggers do not execute if constraints are violated or if some other event causes the table modification to fail.

Like stored procedures, triggers can be nested up to 32 levels deep and can be fired recursively. For more information about trigger nesting and recursion, search the Transact-SQL Reference in SQL Server Books Online for a page titled "CREATE TRIGGER."

Exercise 1: Applying Cascading Referential Integrity Constraints

In this exercise, you will add cascading referential integrity constraints to the BookShopDB database. This new SQL Server 2000 feature takes the place of a common trigger task, performing cascading deletes and updates to foreign keys.

To configure cascading referential integrity on BookShopDB for the TitleID key

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

 USE BookShopDB INSERT Orders (CustomerID, EmployeeID, Amount, OrderDate, DeliveryDate, PaymentID, StatusID) VALUES (10, 1, 30, GetDate(), DATEADD(day, 5, GetDate()), 1, 1) INSERT BookOrders (titleid, orderid) VALUES ('aust1234', 3)

The first INSERT statement adds an order to the Orders table. The OrderDate and DeliveryDate values are provided by functions. The first function returns today's date (as specified by your computer). The second function adds five days onto the value returned by GetDate. The second INSERT statement adds a record to the BookOrders table. These two insert statements are important for testing the effect of enabling cascading referential integrity for the TitleID foreign key in the BookOrders table.

In the Editor pane of the Query window, enter and execute the following Transact-SQL code:

 UPDATE Books SET TitleID = 'AUST1235'  WHERE TitleID = 'AUST1234'

The following error message appears in the Message tab of the Results pane:

 Server: Msg 547, Level 16, State 1, Line 1 UPDATE statement conflicted with COLUMN REFERENCE constraint 'titleid_fk'. The conflict occurred in database 'BookShopDB', table 'BookAuthors', column 'TitleID'. The statement has been terminated.

This error message is caused because the foreign key constraint, titleid_fk, is violated by attempting to change the TitleID primary key value in the Books table.

In the Editor pane of the Query window, enter and execute the following Transact-SQL code:

 ALTER TABLE BookAuthors DROP CONSTRAINT titleid_fk

This ALTER TABLE statement removes the titleid_fk foreign key constraint from the BookAuthors table.

In the Editor pane of the Query window, enter the following Transact-SQL code:

 ALTER TABLE BookAuthors ADD CONSTRAINT titleid_fk FOREIGN KEY (TitleID) REFERENCES Books (TitleID) ON UPDATE CASCADE ON DELETE CASCADE

This ALTER TABLE statement adds cascading referential integrity to the foreign key constraint (titleid_fk) in the BookAuthors table. When an update to the TitleID primary key occurs in the Books table, the corresponding foreign key will be changed in the BookOrders table. The titleid_fk foreign key constraint makes the Books table the parent of the BookAuthors table.

Execute the Transact-SQL code.
In the Editor pane of the Query window, enter and execute the following Transact-SQL code:

 UPDATE Books SET TitleID = 'AUST1235'  WHERE TitleID = 'AUST1234'

An error message appears because the foreign key constraint, titleid2_fk, applied to the BookOrders table was violated by attempting to change the TitleID primary key value in the Books table.

In the Editor pane of the Query window, enter and execute the following Transact-SQL code:

 ALTER TABLE BookOrders DROP CONSTRAINT titleid2_fk ALTER TABLE BookOrders  ADD CONSTRAINT titleid2_fk FOREIGN KEY (TitleID) REFERENCES Books (TitleID)  ON UPDATE CASCADE ON DELETE CASCADE

The first ALTER TABLE statement drops the titleid2_fk constraint from the BookOrders table. The second ALTER TABLE statement adds cascading referential integrity to the titleid2_fk foreign key constraint. The titleid2_fk foreign key constraint makes the Books table the parent of the BookOrders table.

In the Editor pane of the Query window, enter and execute the following Transact-SQL code:

 UPDATE Books SET TitleID = 'AUST1235'  WHERE TitleID = 'AUST1234'

The TitleID is updated in the Books table, and because of cascading referential integrity, it is also updated in the BookAuthors and BookOrders tables.

In the Editor pane of the Query window, enter and execute the following Transact-SQL code:

 SELECT "Books TitleID" = b.titleID, "BookAuthors TitleID" = ba.titleID,  "BookOrders TitleID" = bo.titleID FROM books b INNER JOIN bookauthors ba ON b.titleID=ba.titleID  INNER JOIN bookorders bo ON b.titleID=bo.titleID WHERE b.titleid='aust1235'

The Grids tab of the Results pane shows that the TitleID column has been updated in all three tables.

In the Editor pane of the Query window, enter and execute the following Transact-SQL code:

 DELETE FROM Books where TitleID = 'AUST1235'

Because of cascading referential integrity being defined for deletions, the DELETE statement removes the record from the Books, BookOrders, and BookAuthors tables. You can verify this fact by running the SELECT statement that appears in step 10.

In the Editor pane of the Query window, enter and execute the following Transact-SQL code:

 ALTER TABLE BookAuthors DROP CONSTRAINT titleid_fk ALTER TABLE BookOrders DROP CONSTRAINT titleid2_fk ALTER TABLE BookAuthors  ADD CONSTRAINT titleid_fk FOREIGN KEY (TitleID) REFERENCES Books (TitleID) ALTER TABLE BookOrders  ADD CONSTRAINT titleid2_fk FOREIGN KEY (TitleID) REFERENCES Books (TitleID) INSERT Books (TitleID, Title, Publisher, PubDate, Edition, Cost, SRP, ConditionID, Sold) VALUES ('AUST1234', 'Sense and Sensibility', 'N/A', 1811, 1, 3000, 5900, 3,0) GO

These statements return the database to its previous state.

Lesson Summary

Procedural data integrity is often implemented by using triggers. Triggers are invoked when table data is modified by an INSERT, UPDATE, or DELETE event. Depending on the trigger class, a trigger can be applied to a table or view. Triggers extend data integrity by enforcing complex business rules in the database. Always implement constraints and rules before using triggers, because triggers will degrade performance more quickly than constraints or rules. There are certain instances in which a trigger is the best option (for example, when custom error messages are required). Triggers can reference data inside or outside the current database. A single trigger can run multiple tasks and can be fired by more than one event. You cannot create a trigger on a temporary or system table, but these objects can be referenced in a trigger. Some events, such as a truncate table event, do not cause a DELETE trigger to fire. Triggers use Inserted and Deleted pseudo tables to store modified or deleted data. These pseudo tables are referenced in the trigger code. There are two classes of triggers: INSTEAD OF and AFTER. INSTEAD OF triggers bypass the view or table modification that fired them and the code in the trigger runs. AFTER triggers fire right after table constraints are processed. If the INSERT, UPDATE, or DELETE event does not complete properly, the trigger rolls back the transaction.