CREATE TRIGGER

You can use the CREATE TRIGGER statement to create a trigger. A trigger is a database object associated with a table. The trigger fires when a specified event occurs in the table.

The syntax 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 parameter specifies the table with which the trigger is associated. The table must not be a temporary table. You cannot associate a trigger with a temporary table or view.

Trigger names exist in the namespace of the schema. Therefore, each trigger must have a unique name in the schema. Triggers in different schemas can have the same name.

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

trigger_time specifies whether the trigger fires before or after each row is modified. Valid values are BEFORE and AFTER. The basic check on column values is performed before the trigger fires. Therefore, you cannot use the BEFORE trigger to convert column values that do not meet the data type into valid values.

trigger_event specifies the type of operation that fires the trigger. Valid values:

• INSERT: The trigger fires each time a new row is inserted into the table. For example, the trigger fires when you use the INSERT, LOAD DATA, or REPLACE statement to insert data into the table.

• UPDATE: The trigger fires each time when a row is modified. For example, the trigger fires when you use the UPDATE statement to update a row.

• DELETE: The trigger fires each time when a row is deleted from the table. For example, the trigger fires when you use the DELETE or REPLACE statement to delete a row from the table. The trigger does not fire when you use the DROP TABLE or TRUNCATE TABLE statement. The DELETE trigger does not fire when a partition is dropped from the table.

trigger_event specifies a table operation type, rather than the literal type of the SQL statements that can fire the trigger. For example, the INSERT trigger can be fired both by the INSERT statement and the LOAD DATA statement. This is because both of them can insert rows into the table.

You can define multiple triggers for tables 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 fire based on their creation sequence.

The trigger_order clause specifies the firing sequence of triggers with the same trigger event and operation time by using the FOLLOWS and PRECEDES keywords. The FOLLOWS keyword indicates that a new trigger will fire after the current trigger. The PRECEDES keyword indicates that a new trigger will fire before the current trigger.

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

In the body of a trigger, you can use aliases OLD and NEW to reference columns in the subject table that is associated with the trigger. OLD.column_name indicates a column of the row to be updated or deleted. NEW.column_name indicates a column of a new row to be inserted or an existing row after it is updated. A trigger cannot use NEW.col_name or OLD.column_name to reference generated columns.

The DEFINER clause specifies the account used for checking the access privileges when the trigger fires. If the DEFINER clause exists, you must set user to an account in the 'user_name'@'host_name' format, such as 'admin'@'oblocalhost', or to the account obtained by using the CURRENT_USER() function. By default, if the DEFINER clause is omitted, the definer is the user who executes the CREATE TRIGGER statement. This is equivalent to DEFINER = CURRENT_USER.

The DEFINER user is involved when trigger privileges are checked.

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

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

  • The TRIGGER privilege on the subject table.

  • The SELECT privilege on the subject table if table columns are referenced by using OLD.col_name or NEW.col_name in the trigger body.

  • The UPDATE privilege on the subject if table columns are assigned values by using SET NEW.col_name = value in the trigger body.

  • Other privileges required by statements executed by the trigger.

In the trigger body, the CURRENT_USER() function is used to indicate that the account used for privilege check when the trigger fires is the DEFINER user but not the user whose action fires the trigger.

For examples of creating triggers, see Triggers.