跳到主要内容

触发器

触发器是与表相关的数据库对象，触发器可以在语句插入、更新或删除关联表中的行时被激活，也可以设置为在触发事件之前或之后激活。

例如，您可以通过 INSERT 或 LOAD DATA 语句插入行，并且每插入一行就会激活一次 INSERT 触发器，如果批量插入两行数据，则会触发两次触发器；您可以在插入表的每一行之前或在更新的每一行之后激活触发器。

触发器的类型​

当前版本的 seekdb 主要支持如下类型的触发器：

  • INSERT 型触发器：表示插入某一行时激活触发器，可以通过 INSERT、LOAD DATA、REPLACE 语句触发。

  • UPDATE 型触发器：表示更改某一行时激活触发器，可以通过 UPDATE 语句触发。

  • DELETE 型触发器：删除某一行时激活触发器，可以通过 DELETE、REPLACE 语句触发。

比较特殊的是 INSERT INTO ... ON DUPLICATE KEY UPDATE 语句，每个行都会激活一个 BEFORE INSERT 触发器，接着是 AFTER INSERT 触发器或者 BEFORE UPDATE 与 AFTER UPDATE 触发器，是 AFTER INSERT 触发器还是 BEFORE UPDATE 与 AFTER UPDATE 触发器主要取决于行是否有重复的键。

触发器的创建​

您可以使用 CREATE TRIGGER 语句创建触发器。

创建触发器的用户需要具备以下权限：

  • 当前触发器关联的表的权限，包括 SELECT、INSERT、UPDATE、DELETE 等权限。

  • 触发器权限，即 CREATE 权限。

  • 触发器激活后要执行的语句的权限。

创建触发器的 SQL 语法如下：

CREATE
    TRIGGER [IF NOT EXISTS] trigger_name
    trigger_time trigger_event
    ON table_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

语法说明如下：

  • IF NOT EXISTS：如果尝试创建的触发器名称已经存在，且未使用 IF NOT EXISTS 选项，则会触发错误消息；而指定了 IF NOT EXISTS 后，不会出现错误，只会生成一条 warning 信息。

  • 触发器名称 trigger_name 必须保持唯一。

  • table_name 表示建立触发器的表名，即在哪张表上建立触发器。

  • BEFORE 或 AFTER 表示触发动作的时间，例如触发器将在每一行插入到表中之前还是之后激活。

  • INSERT、UPDATE 或 DELETE 表示触发事件，即激活触发器的操作类型。

  • FOR EACH ROW 用于定义触发器的主体，每次触发器激活时就会执行该语句，对于受触发事件影响的每一行都会执行一次。

seekdb 中也定义了 NEW.columnName 和 OLD.columnName：

  • 在 INSERT 型触发器中，NEW.columnName 用来表示将要（BEFORE）或已经（AFTER）插入的新数据。其中，columnName 为相应数据表的某一列名。

  • 在 UPDATE 型触发器中，OLD.columnName 用来表示将要或已经被修改的原数据，NEW.columnName 用来表示将要或已经修改后的新数据。

  • 在 DELETE 型触发器中，OLD.columnName 用来表示将要或已经被删除的原数据。

  • OLD.columnName 是只读的，而 NEW.columnName 则可以在触发器中使用 SET 赋值。

示例 1：创建一个触发器 test_trg，并将其与表 test 相关联，以激活 INSERT 操作，且触发器充当累加器，将插入到表的列的值求和。

CREATE TABLE test (user_id INT, user_num DECIMAL(10,2));
Query OK, 0 rows affected

CREATE TRIGGER test_trg BEFORE INSERT ON test
       FOR EACH ROW SET @sum = @sum + NEW.user_num;
Query OK, 0 rows affected

示例 2：创建触发器 trg2_t 和 trg3_t 在触发器 trg1_t 之后执行，触发器 trg4_t 在触发器 trg1_t 之前执行。

CREATE TABLE t(c1 INT);
CREATE TABLE msg(c1 INT AUTO_INCREMENT PRIMARY KEY, c2 VARCHAR(100));
CREATE TRIGGER trg1_t BEFORE INSERT ON t FOR EACH ROW
BEGIN
  INSERT INTO msg(c2) VALUES ('BEFORE INSERT trg1_t');
END;
/
CREATE TRIGGER trg2_t BEFORE INSERT ON t FOR EACH ROW FOLLOWS trg1_t
BEGIN
  INSERT INTO msg(c2) VALUES ('BEFORE INSERT trg2_t');
END;
/
CREATE TRIGGER trg3_t BEFORE INSERT ON t FOR EACH ROW FOLLOWS trg1_t
BEGIN
  INSERT INTO msg(c2) VALUES ('BEFORE INSERT trg3_t');
END;
/

CREATE TRIGGER trg4_t BEFORE INSERT ON t FOR EACH ROW PRECEDES trg1_t
BEGIN
  INSERT INTO msg(c2) VALUES ('BEFORE INSERT trg4_t');
END;
/
INSERT INTO t VALUES (1);

SELECT * FROM msg;

返回结果如下所示：

+----+----------------------+
| c1 | c2                   |
+----+----------------------+
|  1 | BEFORE INSERT trg4_t |
|  2 | BEFORE INSERT trg1_t |
|  3 | BEFORE INSERT trg3_t |
|  4 | BEFORE INSERT trg2_t |
+----+----------------------+
4 rows in set

此外，如果需要定义的触发器有多条执行语句时，可以使用 BEGIN ... END 语句，分别表示整个代码块的开始和结束。

BEGIN ... END 语句的语法如下：

BEGIN
[statement_list]
END

其中，statement_list 表示一个或多个语句的列表，列表内的每条语句都必须使用分号（;）结尾。由于在 SQL 语句中，分号（;）是语句结束的标识符，遇到分号表示该段语句已经结束，系统就会开始执行该段语句，最终导致在执行过程中，解释器因为找不到与 BEGIN 匹配的 END 而报错。为了避免发生此类错误，可以使用 DELIMITER 命令来修改语句结束符。

DELIMITER 命令示例如下：

DELIMITER new_delemiter

其中，new_delemiter 可以设置为 1 个或多个字节长度的符号，默认为分号（;），您可以将其修改为其他符号，例如 #。

添加 DELIMITER 命令后，DELIMITER 命令后的语句再使用分号则不会报错，直到遇到设置的结束符（#），才认为该语句结束。

提示

使用 DELIMITER 命令修改结束符后，待语句执行结束，请务必将结束符修改为默认符号分号（;）。

示例如下：

CREATE TABLE test (user_id INT, user_num DECIMAL(10,2));
Query OK, 0 rows affected

DELIMITER //

CREATE TRIGGER test_trg BEFORE UPDATE ON test
               FOR EACH ROW
               BEGIN
               IF NEW.user_num < 1 THEN
              SET NEW.user_num  = 1;
              ELSEIF NEW.user_num > 45 THEN
              SET NEW.user_num= 45;
             END IF;
             END //
Query OK, 0 rows affected

DELIMITER ;

触发器的使用限制​

seekdb 触发器有以下使用限制：

  • 只能在永久表上创建触发器，不能对临时表创建触发器。

  • 触发器不能使用 CALL 语句将数据返回给客户端或包含动态 SQL 的存储过程，但是允许存储过程或者函数通过 OUT 或者 IN OUT 类型的参数将数据返回触发器。

  • 触发器中不能使用开启或结束事务的语句段。例如，开始事务（START TRANSACTION）、提交事务（COMMIT）或回滚事务（ROLLBACK），但是可以回滚到一个保存点，即 Savepoint 是允许的，因为回滚到保存点不会结束事务。

  • 外键不会激活触发器。

  • 触发器中不允许返回值，因此触发器中不能有返回语句，如果需要立即停止一个触发器，则需要使用 LEAVE 语句。

查看触发器元数据​

要获取触发器相关的元数据，请执行以下操作：

Contents

编辑此页