Creating Triggers

When creating a trigger you need to specify four pieces of information:

• The unique trigger name

• The table to which the trigger is to be associated

• The action that the trigger should respond to (DELETE, INSERT, or UPDATE)

• When the trigger should be executed (before or after processing)

Triggers are created using the CREATE TRIGGER statement. Here is a really simple example:

• Input

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';

• Analysis

CREATE TRIGGER is used to create the new trigger named newproduct. triggers can be executed before or after an operation occurs, and here AFTER INSERT is specified so the trigger will execute after a successful INSERT statement has been executed. The trigger then specifies FOR EACH ROW and the code to be executed for each inserted row. In this example, the text Product added will be displayed once for each row inserted.

To test this trigger, use the INSERT statement to add one or more rows to products; you'll see the Product added message displayed for each succesful insertion.

Triggers are defined per time per event per table, and only one trigger per time per event per table is allowed. As such, up to six triggers are supported per table (before and after each of INSERT, UPDATE, and DELETE). A single trigger cannot be associated with multiple events or multiple tables, so if you need a trigger to be executed for both INSERT and UPDATE operations, you'll need to define two triggers.