Creating Triggers

Triggers are a type of stored procedure designed to run when the data in a table or view is modified. Triggers are attached to an individual table; when a query (an update, insert, or delete query) affects data in the table, the trigger executes.

Because a trigger is really a stored procedure with a controlled execution time (hence the name trigger), it can have quite complex SQL statements and flow execution logic.

To create a trigger, use Server Explorer and locate the table to which the trigger is to be attached. Right-click the table name, select Add New Trigger, and then use the SQL Editor to write the SQL for the trigger. When the trigger is saved to the database, it shows up under its related table in Server Explorer (alongside the columns in the table). Figure 13.20 shows a simple trigger designed to raise an error if an update statement changes the Availability column in the Location table.

FIGURE 13.20 Creating a trigger.