Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

IV.3.1. DML Triggers: Letting Your Database Look After Itself

Triggers are often referred to as a special type of stored procedure. Just as stored procedures can be programmed to perform just about anything within a database, so can triggers. Data Manipulation Language (DML) triggers are often used to

• Make changes to other columns (in the same table or other tables within the database) in response to a data modification.

• Perform auditing by recording who made a data modification and when.

• Roll back or undo data modifications.

• Provide specialized error messages.

Although a stored procedure can be executed with the EXEC command, Data Manipulation Language (DML) triggers can't be called directly. Instead, they are configured on individual tables or views and respond to the following DML commands:

• INSERT: When an INSERT command is executed on a table, an INSERT trigger on the table fires.

  For example, when an order is placed (inserting data into the Orders table), you might like to update the on-hand inventory column in the Products table. You could create an INSERT trigger on the Orders table to do this. When someone orders five glossy widgets, the INSERT trigger would fire and would reduce the on-hand inventory value by five.

• UPDATE: When an UPDATE command is executed on a table, an UPDATE trigger on the table fires.

  For example, management might want to know if anyone ever modifies data (such as the Salary column) in the Employees table. You could create an UPDATE trigger on the Employees ...