Triggers

A trigger is a special kind of stored procedure that goes into effect when you modify data in a specified table using one or more data modification operations: UPDATE, INSERT, or DELETE. Triggers can query other tables and can include complex SQL statements. They are primarily useful for enforcing complex business rules or requirements. For example, you could control whether to allow an order to be inserted based on a customer's current account status.

Triggers are also useful for enforcing referential integrity, which preserves the defined relationships between tables when you add, update, or delete the rows in those tables. However, the best way to enforce referential integrity is to define primary key and foreign key constraints in the related tables. If you use database diagrams, you can create a relationship between tables to automatically create a foreign key constraint. For details, see Table Relationships.

Advantages of Using Triggers

Triggers are useful in these ways:

• Triggers are automatic: they are activated immediately after any modification to the table’s data, such as a manual entry or an application action.
  
  
• Triggers can cascade changes through related tables in the database. For example, you can write a delete trigger on the title_id column of the titles table to cause a deletion of matching rows in other tables. The trigger uses the title_id column as a unique key to locate matching rows in the titleauthor, sales, and roysched tables.
  
  
• Triggers can enforce restrictions that are more complex than those defined with check constraints. Unlike check constraints, triggers can reference columns in other tables. For example, a trigger can roll back updates that attempt to apply a discount (stored in the discounts table) to books (stored in the titles table) with a price of less than $10.

For details about working with triggers, see the following topics: