触发器
📄字数 1.8K
👁️阅读量 加载中...
在 PL/SQL 中，触发器（Trigger） 是一种特殊的存储程序，它会在特定的数据库事件（如 INSERT、UPDATE、DELETE）发生时自动执行。触发器主要用于实现数据完整性约束、审计跟踪或复杂的业务规则。
创建、删除、修改、重编译触发器
在数据库对象章节有相关内容的详细描述，参考触发器。
查看触发器
触发器保存在数据库的字典中，几个常用的字典视图如下：
SYS_TRIGGERS：sys_triggers 系统表用于存储、管理系统库和用户库中所有创建的触发器信息，查看需在系统库下有系统权限。
ALL_TRIGGERS：all_triggers 系统视图用于存储和管理创建的所有触发器信息。
DBA_TRIGGERS：dba_triggers 系统视图用于存储和管理当前库中所有创建的触发器，查看需要在当前库下且有查看权限。
USER_TRIGGERS：user_triggers 系统视图用于查询当前用户创建的所有触发器信息。
示例
sql
SQL> SELECT * FROM sys_triggers;
+-------+-----------+---------+---------+---------+--------------------+-----------+------------+-----------+----------------------+----------+--------+--------------------------+--------+-------+--------+----------+-----------+-----------+
| DB_ID | SCHEMA_ID | USER_ID | OBJ_ID | TRIG_ID | TRIG_NAME | TRIG_TIME | TRIG_EVENT | TRIG_TYPE | TRIG_COND | LANGUAGE | DEFINE | CREATE_TIME |
ENABLE | VALID | IS_SYS | COMMENTS | RESERVED1 | RESERVED2 |
+-------+-----------+---------+---------+---------+--------------------+-----------+------------+-----------+----------------------+----------+--------+--------------------------+--------+-------+--------+----------+-----------+-----------+
| 1 | 1 | 1 | 1048587 | 1048588 | TRG_TEST_NOT_EXIST | 1 | 1 | 1 | <NULL> | PLSQL | <CLOB> | 2025-07-01 19:31:29.403 |
T | F | F | <NULL> | <NULL> | <NULL> |
| 1 | 1 | 1 | 1048582 | 1048589 | TRIG_TEST4 | 4 | 3 | 1 | <NULL> | PLSQL | <CLOB> | 2025-07-01 19:31:29.413 |
T | F | F | <NULL> | <NULL> | <NULL> |
| 1 | 1 | 1 | 1048582 | 1048591 | TRG_TEST2 | 4 | 2 | 1 | ("OLD"."ID") >= (10) | PLSQL | <CLOB> | 2025-07-01 19:33:14.321 |
T | F | F | <NULL> | <NULL> | <NULL> |
+-------+-----------+---------+---------+---------+--------------------+-----------+------------+-----------+----------------------+----------+--------+--------------------------+--------+-------+--------+----------+-----------+-----------+
SQL> SELECT * FROM all_triggers;
+-------+-----------+---------+---------+---------+--------------------+-----------+------------+-----------+----------------------+----------+--------+--------------------------+--------+-------+--------+----------+-----------+-----------+
| DB_ID | SCHEMA_ID | USER_ID | OBJ_ID | TRIG_ID | TRIG_NAME | TRIG_TIME | TRIG_EVENT | TRIG_TYPE | TRIG_COND | LANGUAGE | DEFINE | CREATE_TIME |
ENABLE | VALID | IS_SYS | COMMENTS | RESERVED1 | RESERVED2 |
+-------+-----------+---------+---------+---------+--------------------+-----------+------------+-----------+----------------------+----------+--------+--------------------------+--------+-------+--------+----------+-----------+-----------+
| 1 | 1 | 1 | 1048587 | 1048588 | TRG_TEST_NOT_EXIST | 1 | 1 | 1 | <NULL> | PLSQL | <CLOB> | 2025-07-01 19:31:29.403 |
T | F | F | <NULL> | <NULL> | <NULL> |
| 1 | 1 | 1 | 1048582 | 1048589 | TRIG_TEST4 | 4 | 3 | 1 | <NULL> | PLSQL | <CLOB> | 2025-07-01 19:31:29.413 |
T | F | F | <NULL> | <NULL> | <NULL> |
| 1 | 1 | 1 | 1048582 | 1048591 | TRG_TEST2 | 4 | 2 | 1 | ("OLD"."ID") >= (10) | PLSQL | <CLOB> | 2025-07-01 19:33:14.321 |
T | F | F | <NULL> | <NULL> | <NULL> |
+-------+-----------+---------+---------+---------+--------------------+-----------+------------+-----------+----------------------+----------+--------+--------------------------+--------+-------+--------+----------+-----------+-----------+
SQL> SELECT * FROM dba_triggers;
+-------+-----------+---------+---------+---------+--------------------+-----------+------------+-----------+----------------------+----------+--------+--------------------------+--------+-------+--------+----------+-----------+-----------+
| DB_ID | SCHEMA_ID | USER_ID | OBJ_ID | TRIG_ID | TRIG_NAME | TRIG_TIME | TRIG_EVENT | TRIG_TYPE | TRIG_COND | LANGUAGE | DEFINE | CREATE_TIME |
ENABLE | VALID | IS_SYS | COMMENTS | RESERVED1 | RESERVED2 |
+-------+-----------+---------+---------+---------+--------------------+-----------+------------+-----------+----------------------+----------+--------+--------------------------+--------+-------+--------+----------+-----------+-----------+
| 1 | 1 | 1 | 1048587 | 1048588 | TRG_TEST_NOT_EXIST | 1 | 1 | 1 | <NULL> | PLSQL | <CLOB> | 2025-07-01 19:31:29.403 |
T | F | F | <NULL> | <NULL> | <NULL> |
| 1 | 1 | 1 | 1048582 | 1048589 | TRIG_TEST4 | 4 | 3 | 1 | <NULL> | PLSQL | <CLOB> | 2025-07-01 19:31:29.413 |
T | F | F | <NULL> | <NULL> | <NULL> |
| 1 | 1 | 1 | 1048582 | 1048591 | TRG_TEST2 | 4 | 2 | 1 | ("OLD"."ID") >= (10) | PLSQL | <CLOB> | 2025-07-01 19:33:14.321 |
T | F | F | <NULL> | <NULL> | <NULL> |
+-------+-----------+---------+---------+---------+--------------------+-----------+------------+-----------+----------------------+----------+--------+--------------------------+--------+-------+--------+----------+-----------+-----------+
SQL> SELECT * FROM user_triggers;
+-------+-----------+---------+---------+---------+--------------------+-----------+------------+-----------+----------------------+----------+--------+--------------------------+--------+-------+--------+----------+-----------+-----------+
| DB_ID | SCHEMA_ID | USER_ID | OBJ_ID | TRIG_ID | TRIG_NAME | TRIG_TIME | TRIG_EVENT | TRIG_TYPE | TRIG_COND | LANGUAGE | DEFINE | CREATE_TIME |
ENABLE | VALID | IS_SYS | COMMENTS | RESERVED1 | RESERVED2 |
+-------+-----------+---------+---------+---------+--------------------+-----------+------------+-----------+----------------------+----------+--------+--------------------------+--------+-------+--------+----------+-----------+-----------+
| 1 | 1 | 1 | 1048587 | 1048588 | TRG_TEST_NOT_EXIST | 1 | 1 | 1 | <NULL> | PLSQL | <CLOB> | 2025-07-01 19:31:29.403 |
T | F | F | <NULL> | <NULL> | <NULL> |
| 1 | 1 | 1 | 1048582 | 1048589 | TRIG_TEST4 | 4 | 3 | 1 | <NULL> | PLSQL | <CLOB> | 2025-07-01 19:31:29.413 |
T | F | F | <NULL> | <NULL> | <NULL> |
| 1 | 1 | 1 | 1048582 | 1048591 | TRG_TEST2 | 4 | 2 | 1 | ("OLD"."ID") >= (10) | PLSQL | <CLOB> | 2025-07-01 19:33:14.321 |
T | F | F | <NULL> | <NULL> | <NULL> |
+-------+-----------+---------+---------+---------+--------------------+-----------+------------+-----------+----------------------+----------+--------+--------------------------+--------+-------+--------+----------+-----------+-----------+反汇编触发器
将触发器反汇编为类似汇编语言的指令码输出。