CREATE TRIGGER
语法
CREATE [OR REPLACE]
[DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
TRIGGER [IF NOT EXISTS] trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW
[{ FOLLOWS | PRECEDES } other_trigger_name ]
trigger_stmt
Contents
描述
该语句用于创建触发器trigger。触发器是数据库中关联到表上的命名对象，当关联表上发生某些特定事件的时候将激活触发器。触发器的关联表tbl_name必须是一个永久表，不能为临时表TEMPORARY和视图view创建触发器。
CREATE TRIGGER要求关联表上的TRIGGER权限。
MariaDB starting with 10.2.3
可以为相同的触发时间(trigger_time,before/after)和触发事件(trigger_event,delete/update/insert)创建多个触发器。
OR REPLACE
MariaDB starting with 10.1.4
如果触发器已存在，则会先删除再重新定义新的触发器，而不是报错。
DEFINER
DEFINER子句决定了激活触发器时检测触发器权限上下文。
IF NOT EXISTS
MariaDB starting with 10.1.4
当使用了IF NOT EXISTS子句时，将只有该名称的trigger不存在时才会创建，如果该名称的trigger已存在，则会给出warning信息。
trigger_time
trigger_time指的是触发器的激活时间。值BEFORE和AFTER分别表示当表中的行被修改前、修改后激活触发器。
trigger_event
trigger_event指的是激活触发器的语句，即修改表中行的方式。trigger_event可以为以下几种值：
INSERT: 当任意新行插入到表中的时候激活触发器。例如，通过INSERT、LOAD DATA和REPLACE语句。UPDATE: 当表中的行被修改时激活触发器。例如，通过UPDATE语句。DELETE: 当表中的任意行被删除时激活触发器。例如，通过DELETE语句、REPLACE语句。但是对表使用DROP TABLE和TRUNCATE语句时不会激活触发器(这些是DDL语句，MySQL和MariaDB不支持DDL触发器)，因为它们不会使用DELETE动作，drop分区的时候也不会激活DELETE触发器。
FOLLOWS/PRECEDES other_trigger_name
MariaDB starting with 10.2.3
The FOLLOWS other_trigger_name and PRECEDES other_trigger_name options were added in MariaDB 10.2.3 as part of supporting multiple triggers per action time.
This is the same syntax used by MySQL 5.7, although MySQL 5.7 does not have multi-trigger support.
FOLLOWS adds the new trigger after another trigger while PRECEDES adds the new trigger before another trigger. If neither option is used, the new trigger is added last for the given action and time.
FOLLOWS and PRECEDES are not stored in the trigger definition. However the trigger order is guaranteed to not change over time. mysqldump and other backup methods will not change trigger order.
You can verify the trigger order from the ACTION_ORDER column in INFORMATION_SCHEMA.TRIGGERS table.
SELECT trigger_name, action_order FROM information_schema.triggers WHERE event_object_table='t1';
示例
CREATE DEFINER=`root`@`localhost` TRIGGER increment_animal AFTER INSERT ON animals FOR EACH ROW UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
OR REPLACE 和 IF NOT EXISTS 相关示例
CREATE DEFINER=`root`@`localhost` TRIGGER increment_animal AFTER INSERT ON animals FOR EACH ROW UPDATE animal_count SET animal_count.animals = animal_count.animals+1; ERROR 1359 (HY000): Trigger already exists CREATE OR REPLACE DEFINER=`root`@`localhost` TRIGGER increment_animal AFTER INSERT ON animals FOR EACH ROW UPDATE animal_count SET animal_count.animals = animal_count.animals+1; Query OK, 0 rows affected (0.12 sec) CREATE DEFINER=`root`@`localhost` TRIGGER IF NOT EXISTS increment_animal AFTER INSERT ON animals FOR EACH ROW UPDATE animal_count SET animal_count.animals = animal_count.animals+1; Query OK, 0 rows affected, 1 warning (0.00 sec) SHOW WARNINGS; +-------+------+------------------------+ | Level | Code | Message | +-------+------+------------------------+ | Note | 1359 | Trigger already exists | +-------+------+------------------------+ 1 row in set (0.00 sec)
