OceanBase 数据库支持 DML 行触发器，类型包括如下：

  • BEFORE INSERT FOR EACH ROW

  • AFTER INSERT FOR EACH ROW

  • BEFORE UPDATE FOR EACH ROW

  • AFTER UPDATE FOR EACH ROW

  • BEFORE DELETE FOR EACH ROW

  • AFTER DELETE FOR EACH ROW

触发器名与过程名和包的名字不一样，它是单独的名字空间，因而触发器名可以和表或过程有相同的名字。

创建触发器

创建触发器的语法如下：

  1. CREATE [OR REPLACE] TRIGGER trigger_name
  2. {BEFORE | AFTER }
  3. {INSERT | DELETE | UPDATE [OF column [, column …]]}
  4. ON [schema.] table_name 
  5. [REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
  6. FOR EACH ROW
  7. [WHEN condition]
  8. trigger_body;

参数解释如下表：

参数

含义

说明

BEFORE 和 AFTER

指出触发器的触发时序分别为前触发和后触发方式。

前触发是在执行触发事件之前触发当前所创建的触发器，后触发是在执行触发事件之后触发当前所创建的触发器。

FOR EACH ROW

说明触发器为行触发器

行触发器要求当一个 DML 语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器。

REFERENCING

说明相关名称，在行触发器的 PL 块和 WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为 OLD 和 NEW

触发器的 PL 块中应用相关名称时，必须在它们之前加冒号(:)，但在 WHEN 子句中则不能加冒号。

WHEN

说明触发约束条件

Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用 PL 函数。WHEN 子句指定的触发约束条件只能用在 BEFORE 和 AFTER 行触发器中。

注意

当一个基表被修改(INSERT, UPDATE, DELETE)时要执行的存储过程，执行时根据其所依附的基表改动而自动触发，因此与应用程序无关，用触发器可以保证数据的一致性和完整性。

触发器使用限制

触发器有如下使用限制：

  • 触发器内部的 SELECT 语句只能为SELECT … INTO …结构，或者为定义游标所使用的 SELECT 语句。

  • 触发器中不能使用数据库事务控制语句 COMMIT，ROLLBACK 和 SVAEPOINT 语句。

  • 由触发器所调用的过程或函数也不能使用数据库事务控制语句。

OLD 和 NEW 伪记录

当触发器被触发时，要引用被插入（INSERT）、更新（UPDATE）或删除（DELETE）的记录中的列值，有时要使用操作前或操作后列的值，语法如下：

  • :NEW 用于修饰符访问操作完成后列的值

  • :OLD 用于修饰符访问操作完成前列的值

使用有效性如下表：

特性

INSERT

UPDATE

DELETE

OLD

NULL

有效

有效

NEW

有效

有效

NULL

示例：一个简单的触发器

  1. CREATE TABLE reg_his AS SELECT * FROM REGIONS WHERE 1=2;
  3. CREATE OR REPLACE TRIGGER del_new_region
  4.    BEFORE DELETE ON HR.REGIONS 
  5.    FOR EACH ROW
  6.    WHEN (old.region_id > 3)
  7. BEGIN
  8.    INSERT INTO reg_his(region_id , region_name )
  9.        VALUES( :old.region_id, :old.region_name );
  10. END;

示例：当对 HR.REGIONS 表进行删除操作时，把 region_id 大于 3 的记录移动到 reg_his 表中

  1. obclient> select * from regions;
  2. +-----------+------------------------+
  3. | REGION_ID | REGION_NAME            |
  4. +-----------+------------------------+
  5. |         1 | Europe                 |
  6. |         4 | Middle East and Africa |
  7. |         3 | Asia                   |
  8. |         2 | Americas               |
  9. +-----------+------------------------+
  10. 4 rows in set (0.00 sec)
  12. obclient> select * from reg_his;
  13. Empty set (0.00 sec)
  15. obclient> delete from regions where region_id>2;
  16. Query OK, 2 rows affected (0.29 sec)
  18. obclient> select * from regions;
  19. +-----------+-------------+
  20. | REGION_ID | REGION_NAME |
  21. +-----------+-------------+
  22. |         1 | Europe      |
  23. |         2 | Americas    |
  24. +-----------+-------------+
  25. 2 rows in set (0.00 sec)
  27. obclient> select * from reg_his;
  28. +-----------+------------------------+
  29. | REGION_ID | REGION_NAME            |
  30. +-----------+------------------------+
  31. |         4 | Middle East and Africa |
  32. +-----------+------------------------+
  33. 1 row in set (0.00 sec)
  35. obclient>