CREATE TRIGGER

CREATE TRIGGER 语句用于在普通表上创建行级触发器。 当指定的 INSERT、UPDATE 或 DELETE 事件发生时， 触发器会自动执行定义的 SQL 语句或 BEGIN ... END 复合语句。

语法图

CreateTriggerStmt ::=
    'CREATE' TriggerDefiner 'TRIGGER' IfNotExists TriggerName
    TriggerTiming TriggerEvent 'ON' TableName
    'FOR' 'EACH' 'ROW' TriggerOrder TriggerBody

TriggerDefiner ::=
    ( 'DEFINER' '=' Username )?

IfNotExists ::=
    ( 'IF' 'NOT' 'EXISTS' )?

TriggerName ::= TableName

TriggerTiming ::=
    'BEFORE'
|   'AFTER'

TriggerEvent ::=
    'INSERT'
|   'UPDATE'
|   'DELETE'

TriggerOrder ::=
    ( ( 'FOLLOWS' | 'PRECEDES' ) Identifier )?

TriggerBody ::=
    A single SQL statement or a 'BEGIN' ... 'END' compound statement

语法说明

CREATE [DEFINER = user] TRIGGER [IF NOT EXISTS] trigger_name
    {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
    ON tbl_name FOR EACH ROW
    [FOLLOWS other_trigger_name | PRECEDES other_trigger_name]
    trigger_body

参数说明

使用说明

• 创建、删除和查看触发器时，需要拥有目标表上的 TRIGGER 权限。
• 触发器只能定义在普通表上，不支持视图、序列、临时表或系统库中的表。
• OLD 伪记录仅可用于 UPDATE 和 DELETE 触发器；NEW 伪记录仅可用于 INSERT 和 UPDATE 触发器。
• NEW 伪记录只能在 BEFORE INSERT 和 BEFORE UPDATE 触发器中修改。
• 触发器体不能直接返回结果集。如果需要查询结果，请使用 SELECT ... INTO 将结果写入变量。
• 如果没有显式指定 FOLLOWS 或 PRECEDES，多个同类触发器默认按创建顺序执行。

示例

在 BEFORE INSERT 触发器中修改待写入的行

CREATE TABLE trig_demo (
    id INT PRIMARY KEY,
    v INT,
    note VARCHAR(20)
);

CREATE TRIGGER trg_fill
BEFORE INSERT ON trig_demo
FOR EACH ROW
SET NEW.note = CONCAT('v=', NEW.v);

INSERT INTO trig_demo(id, v) VALUES (1, 10), (2, 20);
SELECT * FROM trig_demo ORDER BY id;

+----+------+------+
| id | v    | note |
+----+------+------+
|  1 |   10 | v=10 |
|  2 |   20 | v=20 |
+----+------+------+

在 AFTER INSERT 触发器中写入审计表

CREATE TABLE trig_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    msg VARCHAR(50)
);

CREATE TRIGGER trg_log
AFTER INSERT ON trig_demo
FOR EACH ROW
INSERT INTO trig_log(msg)
VALUES (CONCAT('insert:', NEW.id, ':', NEW.note));

INSERT INTO trig_demo(id, v) VALUES (3, 30);
SELECT * FROM trig_log ORDER BY id;

+----+---------------+
| id | msg           |
+----+---------------+
|  1 | insert:3:v=30 |
+----+---------------+

通过 FOLLOWS 和 PRECEDES 指定执行顺序

CREATE TABLE trig_order (
    id INT PRIMARY KEY,
    v INT
);

CREATE TABLE trig_order_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    msg VARCHAR(10)
);

CREATE TRIGGER trg_a
BEFORE INSERT ON trig_order
FOR EACH ROW
INSERT INTO trig_order_log(msg) VALUES ('a');

CREATE TRIGGER trg_b
BEFORE INSERT ON trig_order
FOR EACH ROW
FOLLOWS trg_a
INSERT INTO trig_order_log(msg) VALUES ('b');

CREATE TRIGGER trg_c
BEFORE INSERT ON trig_order
FOR EACH ROW
PRECEDES trg_b
INSERT INTO trig_order_log(msg) VALUES ('c');

INSERT INTO trig_order VALUES (1, 1);
SELECT GROUP_CONCAT(msg ORDER BY id SEPARATOR '') AS order_seen FROM trig_order_log;

+------------+
| order_seen |
+------------+
| acb        |
+------------+

查看和删除触发器

创建完成后，可以使用以下语句查看或删除触发器：

SHOW TRIGGERS LIKE 'trg_%';
SHOW CREATE TRIGGER trg_fill;
DROP TRIGGER trg_fill;

MySQL 兼容性

TiDB 支持 CREATE TRIGGER、DROP TRIGGER、SHOW TRIGGERS 和 SHOW CREATE TRIGGER。 如发现任何兼容性差异，请尝试 TiDB 支持资源。

另请参阅

• CREATE TABLE
• SHOW PRIVILEGES
• 与 MySQL 兼容性对比