MySQL in a Nutshell, 2nd Edition

Name

CREATE TRIGGER

Synopsis

CREATE
[DEFINER = {'user'@'host'|CURRENT_USER}]
TRIGGER trigger {AFTER|BEFORE}
{DELETE|INSERT|UPDATE}
ON table FOR EACH ROW statement

Only one of each trigger timing and trigger event combination is allowed for each table. For example, a table cannot have two BEFORE INSERT triggers, but it can have a BEFORE INSERT and an AFTER INSERT trigger.

To specify that the trigger be executed immediately before the associated user statement, use the parameter BEFORE; to indicate that the trigger should be executed immediately afterward, use AFTER.

At this time, only three types of SQL statements can cause the server to execute a trigger: insertions, deletions, and updates. Specifying INSERT, however, applies the trigger to INSERT statements, LOAD DATA statements, and REPLACE statements—all statements that are designed to insert data into a table. Similarly, specifying DELETE includes both DELETE and REPLACE statements because REPLACE potentially deletes rows as well as inserting them.

Triggers are actions to be taken when a user requests a change to data. Each trigger is associated with a particular table and includes definitions related to timing and event. A trigger timing indicates when a trigger is to be performed (i.e., BEFORE or AFTER). A trigger event is the action that causes the trigger to be executed (i.e., a DELETE, INSERT, or UPDATE on a specified table).

After specifying the trigger event, give the keyword ON followed by the table name. This is followed by ...