Chapter 18. Triggers

MySQL 5.0

Chapter 18. Triggers

Support for triggers is included beginning with MySQL 5.0.2. A trigger is a named database object that is associated with a table and that is activated when a particular event occurs for the table. For example, the following statements create a table and an trigger. The trigger sums the values inserted into one of the table's columns:

mysql> 
Query OK, 0 rows affected (0.03 sec)

mysql> 
    -> 
Query OK, 0 rows affected (0.06 sec)

This chapter describes the syntax for creating and dropping triggers, and shows some examples of how to use them. Discussion of restrictions on use of triggers is given in Section I.1, “Restrictions on Stored Routines and Triggers”. Remarks regarding binary logging as it applies to triggers are given in Section 17.4, “Binary Logging of Stored Routines and Triggers”.

18.1. CREATE TRIGGER Syntax

CREATE
    [DEFINER = {  | CURRENT_USER }]
    TRIGGER   
    ON  FOR EACH ROW 

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. was added in MySQL 5.0.2. Currently, its use requires the privilege.

The trigger becomes associated with the table named , which must refer to a permanent table. You cannot associate a trigger with a table or a view.

When the trigger is activated, the clause determines the privileges that apply, as described later in this section.

is the trigger action time. It can be or to indicate that the trigger activates before or after the statement that activated it.

indicates the kind of statement that activates the trigger. The can be one of the following:

  • : The trigger is activated whenever a new row is inserted into the table; for example, through , , and statements.

  • : The trigger is activated whenever a row is modified; for example, through statements.

  • : The trigger is activated whenever a row is deleted from the table; for example, through and statements. However, and statements on the table do not activate this trigger, because they do not use . See Section 13.2.9, “ Syntax”.

It is important to understand that the 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 trigger is activated by not only statements but also statements because both statements insert rows into a table.

A potentially confusing example of this is the syntax: a trigger will activate for every row, followed by either an trigger or both the and triggers, depending on whether there was a duplicate key for the row.

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

is the statement to execute when the trigger activates. If you want to execute multiple statements, use the compound statement construct. This also enables you to use the same statements that are allowable within stored routines. See Section 17.2.5, “ Compound Statement Syntax”. Some statements are not allowed in triggers; see Section I.1, “Restrictions on Stored Routines and Triggers”.

Note: Currently, triggers are not activated by cascaded foreign key actions. This limitation will be lifted as soon as possible.

Note: Before MySQL 5.0.10, triggers cannot contain direct references to tables by name. Beginning with MySQL 5.0.10, you can write triggers such as the one named shown in this example:

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  b4 INT DEFAULT 0
);

DELIMITER |

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;  
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END;
|

DELIMITER ;

INSERT INTO test3 (a3) VALUES 
  (NULL), (NULL), (NULL), (NULL), (NULL), 
  (NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES 
  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

Suppose that you insert the following values into table as shown here:

mysql> 
    -> 
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

As a result, the data in the four tables will be as follows:

mysql> 
+------+
| a1   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> 
+------+
| a2   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> 
+----+
| a3 |
+----+
|  2 |
|  5 |
|  6 |
|  9 |
| 10 |
+----+
5 rows in set (0.00 sec)

mysql> 
+----+------+
| a4 | b4   |
+----+------+
|  1 |    3 |
|  2 |    0 |
|  3 |    1 |
|  4 |    2 |
|  5 |    0 |
|  6 |    0 |
|  7 |    1 |
|  8 |    1 |
|  9 |    0 |
| 10 |    0 |
+----+------+
10 rows in set (0.00 sec)

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

The clause specifies the MySQL account to be used when checking access privileges at trigger activation time. It was added in MySQL 5.0.17. If a value is given, it should be a MySQL account in '@'' format (the same format used in the statement). The and values both are required. also can be given as . The default value is the user who executes the statement. (This is the same as .)

If you specify the clause, you cannot set the value to any account but your own unless you have the privilege. These rules determine the legal user values:

  • If you do not have the privilege, the only legal value is your own account, either specified literally or by using . You cannot set the definer to some other account.

  • If you have the privilege, you can specify any syntactically legal account name. If the account does not actually exist, a warning is generated.

    Although it is possible to create triggers with a non-existent value, it is not a good idea for such triggers to be activated until the definer actually does exist. Otherwise, the behavior with respect to privilege checking is undefined.

Note: Because MySQL currently requires the privilege for the use of , only the second of the preceding rules applies. (MySQL 5.1.6 implements the 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 checks trigger privileges like this:

  • At time, the user that issues the statement must have the privilege.

  • At trigger activation time, privileges are checked against the user. This user must have these privileges:

    • The privilege.

    • The privilege for the subject table if references to table columns occur via or in the trigger definition.

    • The privilege for the subject table if table columns are targets of = assignments in the trigger definition.

    • Whatever other privileges normally are required for the statements executed by the trigger.

Before MySQL 5.0.17, MySQL checks trigger privileges like this:

  • At time, the user that issues the statement must have the privilege.

  • At trigger activation time, privileges are checked against the user whose actions cause the trigger to be activated. This user must have whatever privileges normally are required for the statements executed by the trigger.

Note that the introduction of the clause changes the meaning of within trigger definitions: The function evaluates to the trigger value as of MySQL 5.0.17 and to the user whose actions caused the trigger to be activated before 5.0.17.