Skip to main content

CREATE TRIGGER

The CREATE TRIGGER statement is used to create a new trigger. A trigger is a database object associated with a table. It is activated when a specified event occurs on the table.

The syntax of the CREATE TRIGGER statement is as follows:

CREATE
    [DEFINER = user]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON table_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

The table_name specifies the table associated with the trigger. The table must be a non-TEMPORARY table. You cannot associate a trigger with a temporary table or a view.

The trigger name exists in the schema namespace. Therefore, all triggers must have unique names within a schema. Triggers in different schemas can have the same name.

The CREATE TRIGGER statement requires the TRIGGER privilege on the table associated with the trigger. If the DEFINER clause is present, the required privileges depend on the user value.

The trigger_time specifies the trigger operation time as BEFORE or AFTER, indicating whether the trigger is activated before or after modifying each row. Column value checks are performed before the trigger is activated. Therefore, you cannot use a BEFORE trigger to convert invalid values to valid values of the column type.

The trigger_event specifies the type of operation that activates the trigger. Valid values are as follows:

  • INSERT: The trigger is activated every time a new row is inserted into the table. For example, data is inserted into the table by using the INSERT, LOAD DATA, or REPLACE statement.

  • UPDATE: The trigger is activated whenever a row is modified. For example, a row is modified by using the UPDATE statement.

  • DELETE: The trigger is activated every time a row is deleted from the table. For example, a row is deleted from the table by using the DELETE or REPLACE statement. The DROP TABLE and TRUNCATE TABLE statements do not activate this trigger. Deleting a partition also does not activate the DELETE trigger.

The trigger_event specifies the type of table operation, not the literal type of the SQL statement that activates the trigger. For example, an INSERT trigger can be activated by using the INSERT or LOAD DATA statement, both of which insert rows into the table.

You can define multiple triggers for the same table with the same trigger event and operation time. For example, a table can have two BEFORE UPDATE triggers. By default, triggers with the same trigger event and operation time are activated in the order they were created.

The trigger_order clause specifies the order of triggers with the same trigger event and operation time by using the FOLLOWS and PRECEDES keywords. The FOLLOWS keyword indicates that the new trigger is activated after the existing trigger. The PRECEDES keyword indicates that the new trigger is activated before the existing trigger.

The trigger_body specifies the statements to be executed when the trigger is activated. To execute multiple statements, use the BEGIN ... END compound statement.

In the trigger body, you can use the aliases OLD and NEW to reference columns in the main table (the table associated with the trigger). OLD.column_name refers to the columns before the current row is updated or deleted. NEW.column_name refers to the columns of the new row to be inserted or the updated current row. Triggers cannot use NEW.col_name or OLD.column_name to reference generated columns.

The DEFINER clause specifies the account used to check access privileges when the trigger is activated. If the DEFINER clause is present, the user value must be an account specified as 'user_name'@'host_name' (for example, 'admin'@'oblocalhost') or an account obtained by using the CURRENT_USER() function. If you omit the DEFINER clause, the default definer is the user who executed the CREATE TRIGGER statement, which is equivalent to DEFINER = CURRENT_USER.

The DEFINER user is involved in checking the following trigger privileges:

  • The user who created the trigger by using the CREATE TRIGGER statement must have the TRIGGER privilege.

  • When the trigger is activated, the privileges are checked based on the DEFINER user. This user must have the following privileges:

    • The TRIGGER privilege on the main table.

    • If you use OLD.col_name or NEW.col_name in the trigger body to reference table columns, you must have the SELECT privilege on the main table.

    • If table columns are assigned values by using SET NEW.col_name = value in the trigger body, you must have the UPDATE privilege on the main table.

    • Other privileges typically required for the statements executed by the trigger.

In the trigger body, the account returned by the CURRENT_USER() function, which is used to check privileges when the trigger is activated, belongs to the DEFINER user, not the user whose operation caused the trigger to be activated.

For more information about how to create a trigger, see Triggers.