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

描述

该语句用于创建触发器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指的是触发器的激活时间。值BEFOREAFTER分别表示当表中的行被修改前、修改后激活触发器。

trigger_event

trigger_event指的是激活触发器的语句,即修改表中行的方式。trigger_event可以为以下几种值:

  • INSERT: 当任意新行插入到表中的时候激活触发器。例如,通过INSERTLOAD DATAREPLACE语句。
  • UPDATE: 当表中的行被修改时激活触发器。例如,通过UPDATE语句。
  • DELETE: 当表中的任意行被删除时激活触发器。例如,通过DELETE语句、REPLACE语句。但是对表使用DROP TABLETRUNCATE语句时不会激活触发器(这些是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)

See also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.