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)

Tip

Keep Trigger Names Unique per Database In MySQL 5 trigger names must be unique per table, but not per database. This means that two tables in the same database can have triggers of the same name. This is not allowed in other DBMSs where trigger names must be unique per database, and it is very likely that MySQL will make the naming rules stricter in a future release. As such, it is a good idea to use database-wide unique trigger names now.

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.

Note

Only Tables Triggers are only supported on tables, not on views (and not on temporary tables).

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.

Note

When Triggers Fail If a BEFORE TRigger fails, MySQL will not perform the requested operation. In addition, if either a BEFORE trigger or the statement itself fails, MySQL will not exdcute an AFTER trigger (if one exists).