Introduction

This chapter takes a close look at triggers. Triggers are sections of SQL Server programming code that execute when data modification is performed on a table in the database.

Triggers offer a method of constraining data input and performing other tasks when data is added, deleted, or updated in the database. Other methods of constraining data include using constraints and rules. Constraints and defaults are covered in Chapter 3, "Physical Database Design." To learn more about writing programming code that can be implemented by a trigger, see Chapter 9, "Stored Procedures and User -Defined Functions."

Triggers perform an important role in SQL Server 2000: They enable you to control what happens when a user inserts , deletes, or updates data in the tables or views in your database. This control can be used to restrict the values that are inserted, prevent deletion of records, update related tables, store denormalized data, or log actions. Triggers might be used for these reasons and others, but many triggers are used to restrict or constrain data input when traditional constraints are not capable enough. A trigger contains code that is similar to a stored procedure, but it is automatically executed when an INSERT , DELETE , or UPDATE statement is invoked. Triggers are often used to help enforce and follow business logic for your organization.

For instance, imagine a company that has a sales database that records invoice line items in a table. If they decide to implement a minimum sales value for each line item on the invoice, they can use a CHECK constraint to enforce this setting. If they want to create custom minimum value for individual products, then a constraint will not do; they must use a trigger. They can create a new table or add a minimum sales value column to their existing Products table. Because each product can have a different minimum sales value, a trigger can query the table to locate the minimum value and ensure that the current value is above the minimum.