13.1.11 CREATE TRIGGER Syntax

    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

This statement creates a new trigger. A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. The trigger becomes associated with the table named tbl_name, which must refer to a permanent table. You cannot associate a trigger with a TEMPORARY table or a view. CREATE TRIGGER was added in MySQL 5.0.2.

Trigger names exist in the schema namespace, meaning that all triggers must have unique names within a schema. Triggers in different schemas can have the same name.

This section describes CREATE TRIGGER syntax. For additional discussion, see Section 18.3.1, “Trigger Syntax and Examples”.

In MySQL 5.0 CREATE TRIGGER requires the SUPER privilege.

The DEFINER clause determines the security context to be used when checking access privileges at trigger activation time. It was added in MySQL 5.0.17. See later in this section for more information.

trigger_time is the trigger action time. It can be BEFORE or AFTER to indicate that the trigger activates before or after each row to be modified.

trigger_event indicates the kind of operation that activates the trigger. These trigger_event values are permitted:

The trigger_event does not represent a literal type of SQL statement that activates the trigger so much as it represents a type of table operation. For example, an INSERT trigger activates not only for INSERT statements but also LOAD DATA statements because both statements insert rows into a table.

A potentially confusing example of this is the INSERT INTO ... ON DUPLICATE KEY UPDATE ... syntax: a BEFORE INSERT trigger activates for every row, followed by either an AFTER INSERT trigger or both the BEFORE UPDATE and AFTER UPDATE triggers, depending on whether there was a duplicate key for the row.


Cascaded foreign key actions do not activate triggers.

There cannot be multiple triggers for a given table that have the same trigger event and action time. For example, you cannot have two BEFORE UPDATE triggers for a table. But you can have a BEFORE UPDATE and a BEFORE INSERT trigger, or a BEFORE UPDATE and an AFTER UPDATE trigger.

trigger_body is the statement to execute when the trigger activates. To execute multiple statements, use the BEGIN ... END compound statement construct. This also enables you to use the same statements that are permissible within stored routines. See Section 13.6.1, “BEGIN ... END Compound-Statement Syntax”. Some statements are not permitted in triggers; see Section E.1, “Restrictions on Stored Programs”.

Within the trigger body, you can refer to columns in the subject table (the table associated with the trigger) by using the aliases OLD and NEW. OLD.col_name refers to a column of an existing row before it is updated or deleted. NEW.col_name refers to the column of a new row to be inserted or an existing row after it is updated.

MySQL stores the sql_mode system variable setting in effect when a trigger is created, and always executes the trigger body with this setting in force, regardless of the current server SQL mode when the trigger begins executing.

The DEFINER clause specifies the MySQL account to be used when checking access privileges at trigger activation time. If a user value is given, it should be a MySQL account specified as 'user_name'@'host_name' (the same format used in the GRANT statement), CURRENT_USER, or CURRENT_USER(). The default DEFINER value is the user who executes the CREATE TRIGGER statement. This is the same as specifying DEFINER = CURRENT_USER explicitly.

If you specify the DEFINER clause, these rules determine the legal DEFINER user values:

Note: Because MySQL currently requires the SUPER privilege for the use of CREATE TRIGGER, only the second of the preceding rules applies. (MySQL 5.1.6 implements the TRIGGER privilege and requires that privilege for trigger creation, so at that point both rules come into play and SUPER is required only for specifying a DEFINER value other than your own account.)

From MySQL 5.0.17 on, MySQL takes the DEFINER user into account when checking trigger privileges as follows:

Before MySQL 5.0.17, DEFINER is not available and MySQL checks trigger privileges like this:

For more information about trigger security, see Section 18.5, “Access Control for Stored Programs and Views”.

Within a trigger body, the CURRENT_USER() function returns the account used to check privileges at trigger activation time. Consistent with the privilege-checking rules just given, CURRENT_USER() returns the DEFINER user from MySQL 5.0.17 on. Before 5.0.17, CURRENT_USER() returns the user whose actions caused the trigger to be activated. For information about user auditing within triggers, see Section 6.3.8, “SQL-Based MySQL Account Activity Auditing”.

If you use LOCK TABLES to lock a table that has triggers, the tables used within the trigger are also locked, as described in Section, “LOCK TABLES and Triggers”.

For additional discussion of trigger use, see Section 18.3.1, “Trigger Syntax and Examples”.