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.
MySQL Enterprise.
For expert advice on creating triggers subscribe to the MySQL Enterprise Monitor. For more information see, http://www.mysql.com/products/enterprise/advisors.html.
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. However, DROP TABLE and TRUNCATE statements on the table do not activate this trigger, because they do not use DELETE. See Section 11.2.9, “TRUNCATE Syntax”.
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 statements that are allowable within stored routines. See Section 17.2.5, “BEGIN ... END Compound Statement Syntax”. Some statements are not allowed in triggers; see Section F.1, “Restrictions on Stored Routines and Triggers”.
MySQL stores the sql_mode system variable setting that is in effect at the time a trigger is created, and always executes the trigger with this setting in force, regardless of the current server SQL mode.
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 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.
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
-> FOR EACH ROW SET @sum = @sum + NEW.amount;
mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
| 1852.48 |
+-----------------------+
In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used; there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated
CREATE TABLE member (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(32),
create_date TIMESTAMP DEFAULT '0000-00-00 00:00:00',
update_date TIMESTAMP DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (id)
);
DELIMITER //
CREATE TRIGGER member_before_insert
BEFORE INSERT ON member
FOR EACH ROW
BEGIN
SET NEW.create_date = NOW(), NEW.update_date = '0000-00-00 00:00:00';
END;//
CREATE TRIGGER member_before_update
BEFORE UPDATE ON member
FOR EACH ROW
BEGIN
SET NEW.update_date = NOW(), NEW.create_date = OLD.create_date;
END;//
DELIMITER ;
Testing Example 1.
> INSERT INTO member (name) VALUES ('newton'), ('albert'), ('witten');
> UPDATE member SET name='einstein' WHERE id=2;
> SELECT * FROM member;
+----+----------+---------------------+---------------------+
| id | name | create_date | update_date
+----+----------+---------------------+---------------------+
| 1 | newton | 2006-06-12 04:16:05 | 0000-00-00 00:00:00 |
| 2 | einstein | 2006-06-12 04:16:05 | 2006-06-12 04:16:36 |
| 3 | witten | 2006-06-12 04:16:05 | 0000-00-00 00:00:00 |
+----+----------+---------------------+---------------------+
CREATE TABLE member_audit_trail(
event_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
id SMALLINT UNSIGNED DEFAULT NULL,
old_name VARCHAR(32) DEFAULT NULL,
action ENUM('insert', 'update', 'delete') NOT NULL,
name VARCHAR(32) DEFAULT NULL,
changed DATETIME DEFAULT NULL,
PRIMARY KEY (event_id)
);
DELIMITER //
CREATE TRIGGER member_after_update
AFTER UPDATE ON member
FOR EACH ROW
BEGIN
INSERT INTO member_audit_trail
SET action='update', id=OLD.id, old_name=OLD.name, name=NEW.name, changed=NOW();
END;//
CREATE TRIGGER member_after_insert
AFTER INSERT ON member
FOR EACH ROW
BEGIN
INSERT INTO member_audit_trail
SET action='insert', id = NEW.id, name = NEW.name, changed=NOW();
END;//
CREATE TRIGGER member_after_delete
AFTER DELETE ON member
FOR EACH ROW
BEGIN
INSERT INTO member_audit_trail
SET action='delete', id=OLD.id, old_name=OLD.name, changed=NOW();
END;//
DELIMITER ;
# The edits we made in Example 1 show up in the audit trail.
> SELECT * FROM member_audit_trail;
+----------+------+----------+--------+----------+---------------------+|
event_id | id | old_name | action | name | changed |+----------+------+----------+--------+----------+---------------------+
| 1 | 1 | NULL | insert | newton | 2006-06-12 04:16:05 |
| 2 | 2 | NULL | insert | albert | 2006-06-12 04:16:05 |
| 3 | 3 | NULL | insert | witten | 2006-06-12 04:16:05 |
| 4 | 2 | albert | update | einstein | 2006-06-12 04:16:36 |+----------+------+----------+--------+----------+--------------------
