(mysql.info) create-trigger
Info Catalog
(mysql.info) triggers
(mysql.info) triggers
(mysql.info) drop-trigger
18.1 `CREATE TRIGGER' Syntax
============================
CREATE
[DEFINER = { USER | CURRENT_USER }]
TRIGGER TRIGGER_NAME TRIGGER_TIME TRIGGER_EVENT
ON TBL_NAME FOR EACH ROW TRIGGER_STMT
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. `CREATE TRIGGER' was added in
MySQL 5.0.2. Currently, its use requires the `SUPER' privilege.
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.
When the trigger is activated, the `DEFINER' clause determines the
privileges that apply, as described later in this section.
TRIGGER_TIME is the trigger action time. It can be `BEFORE' or `AFTER'
to indicate that the trigger activates before or after the statement
that activated it.
TRIGGER_EVENT indicates the kind of statement that activates the
trigger. The TRIGGER_EVENT can be one of the following:
* `INSERT': The trigger is activated whenever a new row is inserted
into the table; for example, through `INSERT', `LOAD DATA', and
`REPLACE' statements.
* `UPDATE': The trigger is activated whenever a row is modified; for
example, through `UPDATE' statements.
* `DELETE': The trigger is activated whenever a row is deleted from
the table; for example, through `DELETE' and `REPLACE' statements.
It is important to understand that 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 is activated by not only `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 will
activate 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.
There cannot be two triggers for a given table that have the same
trigger action time and event. 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_STMT is the statement to execute when the trigger activates. If
you want to execute multiple statements, use the `BEGIN ... END'
compound statement construct. This also enables you to use the same
DONTPRINTYET statements that are allowable within stored routines. See
begin-end. Some statements are not allowed in triggers; see *Note
DONTPRINTYET statements that are allowable within stored routines. See
begin-end. Some statements are not allowed in triggers; see
routine-restrictions.
* Currently, triggers are not activated by cascaded foreign key
actions. This limitation will be lifted as soon as possible.
* 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 `testref' 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 `test1' as
shown here:
mysql> INSERT INTO test1 VALUES
-> (1), (3), (1), (7), (1), (8), (4), (4);
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> SELECT * FROM test1;
+------+
| a1 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM test2;
+------+
| a2 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
| 2 |
| 5 |
| 6 |
| 9 |
| 10 |
+----+
5 rows in set (0.00 sec)
mysql> SELECT * FROM test4;
+----+------+
| 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 `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.
The `DEFINER' 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 USER value is given, it should be a MySQL account in
`'USER_NAME'@'HOST_NAME'' format (the same format used in the `GRANT'
statement). The USER_NAME and HOST_NAME values both are required.
`CURRENT_USER' also can be given as `CURRENT_USER()'. The default
`DEFINER' value is the user who executes the `CREATE TRIGGER'
statement. (This is the same as `DEFINER = CURRENT_USER'.)
If you specify the `DEFINER' clause, you cannot set the value to any
account but your own unless you have the `SUPER' privilege. These rules
determine the legal `DEFINER' user values:
* If you do not have the `SUPER' privilege, the only legal USER
value is your own account, either specified literally or by using
`CURRENT_USER'. You cannot set the definer to some other account.
* If you have the `SUPER' 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
`DEFINER' 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 `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 checks trigger privileges like this:
* At `CREATE TRIGGER' time, the user that issues the statement must
have the `SUPER' privilege.
* At trigger activation time, privileges are checked against the
`DEFINER' user. This user must have these privileges:
* The `SUPER' privilege.
* The `SELECT' privilege for the subject table if references to
table columns occur via `OLD.COL_NAME' or `NEW.COL_NAME' in
the trigger definition.
* The `UPDATE' privilege for the subject table if table columns
are targets of `SET NEW.COL_NAME = VALUE' 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 `CREATE TRIGGER' time, the user that issues the statement must
have the `SUPER' 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 `DEFINER' clause changes the meaning
of `CURRENT_USER()' within trigger definitions: The `CURRENT_USER()'
function evaluates to the trigger `DEFINER' value as of MySQL 5.0.17
and to the user whose actions caused the trigger to be activated before
5.0.17.
Info Catalog
(mysql.info) triggers
(mysql.info) triggers
(mysql.info) drop-trigger
automatically generated byinfo2html