触发器

📄字数 7.1K

👁️阅读量加载中...

触发器是数据库中一种特殊类型的存储过程，它并不由用户或应用程序显式调用，也不通过计划任务或脚本手动执行，而是在特定的数据库事件发生时自动触发执行。

常见的触发事件包括对表的数据进行插入、删除或更新操作。当这些操作发生时，若存在关联的触发器，虚谷数据库将自动激活相应触发器并执行其定义的逻辑。触发器可用于维护多个表之间逻辑关联的数据一致性，从而确保数据的相关完整性。触发器常用于加强数据的完整性约束和业务规则，它们可以在数据变更前或变更后执行，从而确保在数据写入数据库前后，能够对数据进行验证、修改或联动处理。

注意

目前仅支持 DML 触发器，暂未支持 DDL 触发器、登录触发器。
同一个表上可以同时创建多个触发器，多个触发器之间的执行顺序按照触发时机以及创建触发器的时间先后顺序决定：
- 触发时机为 BEFORE 的触发器先于触发时机为 AFTER 的触发器
- 触发时机相同时，按照触发器的创建时间先后顺序执行，先创建的先执行
虚谷数据库中默认触发级别为语句级，同一条语句中插入多个记录时（如 INSERT INTO TEST_TRIG VALUES(1, 'str1')(2, 'str2') ），触发器只触发一次。若要使触发器对每一条记录触发需指定 FOR EACH ROW

一、创建触发器

触发器的基础对象可分为表或视图，基于表的触发器支持 AFTER 与 BEFORE 触发时机，基于视图的触发器支持 INSTEAD OF 触发时机。

1.1 语法格式

1.2 参数说明

Cre_Rep：创建模式。
- OR REPLACE：创建或替换。
- NOFORCE：不强制创建或替换，默认。
- FORCE：强制创建或替换。
IF NOT EXISTS：创建触发器时若同名触发器存在则忽略错误。该关键字无法验证已有同名触发器与当前创建触发器属性是否一致。
trigger_name：触发器名。
TriggerActionTime：触发时机。
- BEFORE：触发器在指定事件发生之前执行。
- AFTER：触发器用于在事件发生后执行后续操作，如日志记录或更新其他表。
- INSTEAD OF：触发器用于替代事件的默认行为，主要用于视图的触发器。
TriggerOneEvent：受监视的操作类型。
- INSERT：插入时触发。
- DELETE：删除时触发。
- UPDATE：更新时触发，可以精确监视在某个字段上。UPDATE OF可监视多个字段。
- name_list：为表的列名，只对触发事件为 UPDATE 的触发器有效，当给定列名后，只有在修改动作变更指定列时，触发器才被触发。
name_space：触发器的基表或者基视图名称。
OptTrigParamAlias：重命名 NEW 或 OLD 关键字。
- REFERENCING：为触发器中的 OLD 和 NEW 定义别名。
  - NEW 与 OLD：伪记录，分别代表触发器所在行的“新值”与“旧值”。
    - INSERT 触发时，NEW 代表插入中的新值，OLD 为 NULL。
    - UPDATE 触发时，NEW 代表更新后的新值，OLD 更新前的旧值。
    - DELETE 触发时，NEW 为 NULL，OLD 删除前的旧值。
  - old_alias: 更新前旧值伪记录的别名。
  - new_alias: 更新后新值伪记录的别名。
TriggerForSpec：触发器触发级别。
- FOR EACH ROW：指定触发器为行级触发器，即每操作一行记录则触发一次。
- FOR STATEMENT：指定触发器为语句级触发器，即每条操作命令触发一次。不指定 TriggerForSpec 时默认为 FOR STATEMENT。
WHEN: 指定触发器约束：指定触发事件时必须满足的条件。
DECLARE VarDefList：变量定义语句。包括变量定义、游标定义、异常定义等，详细信息参见DECLARE 声明。
StmtBlock：触发器的过程体。
- pl_stmt_list: SQL执行语句，详细信息参见SQL执行。
- OptExceptionStmt: 异常处理语句，详细信息参见异常处理。

注意

当过程体含变量定义时，变量定义应在 BEGIN 关键字前通过 DECLARE 关键词显式定义；若无变量定义，则直接从 BEGIN 开始

1.3 示例

示例1：

在表 tab_test_trigger1 上创建名为 trg_test1 的触发器，触发器用于监视表上的插入操作，当插入的记录的ID不小于10 时，将新记录同时插入到表 tab_test_trigger2 中。

SQL

SQL> CREATE TABLE tab_test_trigger1(ID INT, NAME VARCHAR(20));
SQL> CREATE TABLE tab_test_trigger2(ID INT, NAME VARCHAR(20));

SQL> CREATE OR REPLACE TRIGGER trg_test1
       AFTER INSERT ON tab_test_trigger1
       FOR EACH ROW
       WHEN (ID >= 10)
     BEGIN
       INSERT INTO tab_test_trigger2 VALUES (new.id, new.name);
     END;
     /

SQL> INSERT INTO tab_test_trigger1 VALUES(1, 'test_trg_1');
SQL> INSERT INTO tab_test_trigger1 VALUES(10, 'test_trg_10');

SQL> SELECT * FROM tab_test_trigger2;

+----+-------------+
| ID |    NAME     |
+----+-------------+
| 10 | test_trg_10 |
+----+-------------+

(1 row)

示例2：

在表 tab_test_trigger1 上创建名为 trg_test2 的触发器，该触发器用于监视表的ID字段上的更改操作，当被更改的记录原ID值大于等于10时，向 tab_test_trigger2 中插入一条记录，记录ID为 tab_test_trigger1 修改记录的旧值，记录NAME为字符串 trg_update。

SQL

SQL> CREATE OR REPLACE TRIGGER trg_test2
       AFTER UPDATE OF ID ON tab_test_trigger1
       FOR EACH ROW
       WHEN (old.ID >= 10)
     DECLARE
       num INTEGER;
     BEGIN
       INSERT INTO tab_test_trigger2 VALUES (new.id, 'trg_update');
     END;
     /

-- 清空表 tab_test_trigger2 
SQL> TRUNCATE tab_test_trigger2;
-- 修改 tab_test_trigger1 中 ID 大于 10 的数据
SQL> UPDATE tab_test_trigger1 SET ID=11 WHERE ID=10;

SQL> SELECT * FROM tab_test_trigger2;

+----+------------+
| ID |    NAME    |
+----+------------+
| 11 | trg_update |
+----+------------+

(1 row)

示例3：

创建一个使用 referencing 的触发器，referencing new AS nn 表示把 new 关键字重命名为 nn。当对表 tab_test_trigger1插入记录的 ID 大于 1000 或者小于 10 时，自动往表 tab_test_trigger2 插入一条记载消息记录。

SQL

SQL> CREATE OR REPLACE TRIGGER trg_test3
     BEFORE INSERT ON tab_test_trigger1
       REFERENCING new AS nn
       FOR each ROW
     BEGIN
       IF nn.id > 1000 THEN
         INSERT INTO tab_test_trigger2 VALUES (nn.id, '插入了一个过大的数！');
       ELSE
         IF nn.id < 10 THEN
           INSERT INTO tab_test_trigger2 VALUES (nn.id, '插入了一个过小的数！');
         END IF;
       END IF;
     END;
     /

-- 清空表 tab_test_trigger2 
SQL> TRUNCATE tab_test_trigger2;
-- 删除 trg_test1，排除示例1中所定义插入触发器的影响
SQL> DROP TRIGGER trg_test1;
-- 插入数据
SQL> INSERT INTO tab_test_trigger1 VALUES(2, 'test_trg_2');
SQL> INSERT INTO tab_test_trigger1 VALUES(1001, 'test_trg_1001');

SQL> select * from tab_test_trigger2;

+------+----------------------+
|  ID  |         NAME         |
+------+----------------------+
| 2    | 插入了一个过小的数！   |
| 1001 | 插入了一个过大的数！   |
+------+----------------------+

(2 rows)

示例4：

使用 IF NOT EXISTS创建触发器。创建一个与原触发器同名但依赖表不同的触发器，创建时若同名触发器存在则忽略错误。该关键字无法验证已有同名触发器与当前创建触发器属性是否一致。

SQL

SQL> CREATE TABLE tab_test_trg_not_exist1(id int);

SQL> CREATE TRIGGER trg_test_not_exist
     BEFORE INSERT
     ON tab_test_trg_not_exist1
     FOR EACH ROW
     BEGIN
         INSERT INTO tab_test_trg_not_exist1 VALUES(10, 'test_trg_not_exist');
     END;
     /

SQL> SELECT dt.table_name,dr.trig_name FROM user_triggers dr
     JOIN user_tables dt
     ON dr.db_id=dt.db_id AND dr.OBJ_id=dt.table_id 
     WHERE dt.table_name='TAB_TEST_TRG_NOT_EXIST1';

+-------------------------+--------------------+
|       TABLE_NAME        |     TRIG_NAME      |
+-------------------------+--------------------+
| TAB_TEST_TRG_NOT_EXIST1 | TRG_TEST_NOT_EXIST |
+-------------------------+--------------------+

(1 row)

SQL> CREATE TABLE tab_test_trg_not_exist2(id int);

SQL> CREATE TRIGGER IF NOT EXISTS TRG_TEST_NOT_EXIST /*创建一个与原触发器依赖表不同的触发器，此处会返回警告*/
     BEFORE INSERT
     ON tab_test_trg_not_exist2
     FOR STATEMENT
     BEGIN
         INSERT INTO tab_test_trg_not_exist2 VALUES(99);
     END;
     /
Execute successful.

Warning: [E11001] 触发器TRG_TEST_NOT_EXIST已存在

-- 不会对原触发器产生影响
SQL> SELECT dt.table_name,dr.trig_name FROM dba_triggers dr
     JOIN dba_tables dt
     ON dr.db_id=dt.db_id AND dr.OBJ_id=dt.table_id
     WHERE dt.table_name='TAB_TEST_TRG_NOT_EXIST2';

+------------+-----------+
| TABLE_NAME | TRIG_NAME |
+------------+-----------+
+------------+-----------+

(0 rows)

1.4 条件谓词

如果触发器的触发事件超过一种类型，并希望不同触发事件分别执行不同的触发操作时，可以利用条件谓词进行逻辑处理。触发事件 INSERT、DELETE 和 UPDATE，对应的条件谓词分别为 INSERTING、DELETING 和 UPDATING。

若触发器触发事件包括：

sql

INSERT OR DELETE OR UPDATE OF column_name ON table_name

则在触发器处理代码中可使用以下条件进行相关代码逻辑处理：

sql

IF inserting THEN .... END IF;
IF updating THEN .... END IF;
IF deleting THEN .... END IF;

有如下示例：

sql

SQL> CREATE OR REPLACE TRIGGER trig_test4
       AFTER INSERT OR UPDATE OF id, name ON tab_test_trigger1
       FOR each ROW
     BEGIN
       IF updating THEN
         INSERT INTO tab_test_trigger2 VALUES (new.id, 'update tab');
       END IF;
       IF inserting THEN
         INSERT INTO tab_test_trigger2 VALUES (new.id, 'insert tab');
       END IF;
     END;
     /

SQL> SELECT to_char(define) FROM all_triggers WHERE TRIG_NAME='TRIG_TEST4';

+--------------------------------------------------------------+
|                            DEFINE                            |
+--------------------------------------------------------------+
| CREATE TRIGGER trig_test4                                   +|
| AFTER INSERT OR UPDATE OF id, name ON tab_test_trigger1     +|
| FOR each ROW                                                +|
| BEGIN                                                       +|
| IF updating THEN                                            +|
| INSERT INTO tab_test_trigger2 VALUES (new.id, 'update tab');+|
| END IF;                                                     +|
| IF inserting THEN                                           +|
| INSERT INTO tab_test_trigger2 VALUES (new.id, 'insert tab');+|
| END IF;                                                     +|
| END;                                                         |
+--------------------------------------------------------------+

(1 row)

注意

触发器中使用条件谓词时，IF 判断不能嵌套判断，各条件谓词之间需并列处理。

二、修改触发器

支持启用与禁用触发器。

2.1 语法格式

2.2 参数说明

name_space：待操作触发器名称。
('ENABLE' | 'DISABLE')：启用或者禁用触发器。

2.3 示例

sql

-- 以示例 1.3 中 `trg_test3` 为例
SQL> TRUNCATE tab_test_trigger2;
-- 插入数据
SQL> INSERT INTO tab_test_trigger1 VALUES(3, 'test_trg_alter');
SQL> SELECT * FROM tab_test_trigger2;

+----+----------------------+
| ID |         NAME         |
+----+----------------------+
| 3  | 插入了一个过小的数！   |
+----+----------------------+

(1 row)

-- 启用触发器 `trg_test3`
SQL> ALTER TRIGGER trg_test3 DISABLE;
-- 清空数据
SQL> TRUNCATE tab_test_trigger2;
-- 再次插入数据
SQL> INSERT INTO tab_test_trigger1 VALUES(4, 'test_trg_alter');
SQL> SELECT * FROM tab_test_trigger2;

+----+------+
| ID | NAME |
+----+------+
+----+------+

(0 rows)

三、删除触发器

支持删除触发器。

3.1 语法格式

3.2 参数说明

IF EXISTS：触发器存在时删除该触发器，不存在时忽略错误。
name_space：触发器名，名称前也可以加上模式名，形式为模式名.触发器名。

3.3 示例

删除触发器

sql

SQL> DROP TRIGGER trg_test3;

四、重编译失效触发器

重编译失效触发器是指在依赖对象发生变化后，数据库中的触发器因失效而不能正常工作，此时需要通过重新编译的方式恢复其可用性。

提示

虚谷数据库中，触发器失效的原因有：

触发器依赖的对象被删除。
触发器依赖的对象被修改。
触发器依赖的对象被重命名。
触发器依赖的对象被删除并重建。

4.1 语法格式

4.2 示例

步骤1：

创建表并再创建触发器。

sql

-- 创建表
SQL> CREATE TABLE tab_test_recompile1(ID INT, NAME VARCHAR(20));
SQL> CREATE TABLE tab_test_recompile2(ID INT, NAME VARCHAR(20));

-- 创建触发器
SQL> CREATE TRIGGER trg_test_recompile
       AFTER INSERT ON tab_test_recompile1
     FOR EACH ROW
     BEGIN
       INSERT INTO tab_test_recompile2 VALUES (new.id, 'trigger recompile');
     END;
     /

步骤2：

强制修改表中字段名，触发器失效。

sql

-- 强制修改表名
SQL> ALTER TABLE tab_test_recompile1 RENAME id TO new_id CASCADE;

-- 触发器 TRG_TEST_RECOMPILE 已失效
SQL> SELECT trig_name,valid FROM all_triggers WHERE trig_name='trg_test_recompile';

+--------------------+-------+
|     TRIG_NAME      | VALID |
+--------------------+-------+
| TRG_TEST_RECOMPILE | F     |
+--------------------+-------+

(1 row)

SQL> INSERT INTO tab_test_recompile1 VALUES(1, 'insert 1');

Error: [E11008] 触发器TRG_TEST_RECOMPILE被标识为失效

步骤3：

将被修改的字段名还原，重编译存储函数，可正常使用。

sql

-- 字段名强制还原
SQL> ALTER TABLE tab_test_recompile1 RENAME new_id TO id CASCADE;

-- 此时触发器 TRG_TEST_RECOMPILE 仍然失效
SQL> INSERT INTO tab_test_recompile1 VALUES(1, 'insert 1');

Error: [E11008] 触发器TRG_TEST_RECOMPILE被标识为失效

-- 重编译 TRG_TEST_RECOMPILE
SQL> ALTER trigger trg_test_recompile RECOMPILE;

-- 触发器 TRG_TEST_RECOMPILE 已恢复
SQL> INSERT INTO tab_test_recompile1 VALUES(1, 'insert 1');

Total 1 records effected.

SQL> SELECT * FROM tab_test_recompile2;

+----+-------------------+
| ID |       NAME        |
+----+-------------------+
| 1  | trigger recompile |
+----+-------------------+

(1 row)

触发器 ​

一、创建触发器 ​

1.1 语法格式 ​

1.2 参数说明 ​

1.3 示例 ​

1.4 条件谓词 ​

二、修改触发器 ​

2.1 语法格式 ​

2.2 参数说明 ​

2.3 示例 ​

三、删除触发器 ​

3.1 语法格式 ​

3.2 参数说明 ​

3.3 示例 ​

四、重编译失效触发器 ​

4.1 语法格式 ​

4.2 示例 ​

触发器