--=======================
-- PL/SQL --> DML 触发器
--=======================
何谓触发器？简言之，是一段命名的 PL / SQL 代码块，只不过该代码块在特定的条件下被触发并且执行。对于这样的代码我们称之为触发器
。触发器根据触发类型的不同又分为不同级别的触发器，下面将给出触发器的分类，定义，以及使用的示例。
一、触发器的相关概念
1. 触发器的分类
通常根据触发条件以及触发级别的不同分为 DML 触发器， INSTEAD OF 触发器，系统事件触发器。
DML 触发器
ORACLE 对 DML 语句进行触发，可以在 DML 操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。
INSTEAD OF 触发器
在 ORACLE 里，对于简单视图，可以直接使用 DML 进行操作，而复杂视图则不能直接使用 DML ，因此 INSTEAD OF 触发器应运而生。
INSTEAD OF 触发器主要是为解决复杂视图不能执行 DML 而创建。
系统事件触发器
在 ORACLE 数据库系统的事件中进行触发 , 如 ORACLE 系统的启动与关闭等 . 使用系统触发器 , 便于系统跟踪 , 监测数据库变化情况等。
2. 触发器的组成 ( 一段 PL / SQL 代码块，可以由 PL / SQL , Java , C 进行开发 , 特定事件发生将被触发 )
a . 触发事件
Oracle 启动、关闭
Oracle 错误消息
用户登录与断开会话
特定的表、视图上的 DML 操作
基于 schema 的 DDL 操作
b . 触发时间
即该 TRIGGER 是在触发事件发生之前（ BEFORE ）还是之后 (AFTER) 触发，也就是触发事件和该 TRIGGER 的操作顺序。
c . 触发器本身
指实际的触发代码，当触发事件发生后，触发器代码决定将做何种操作。
触发器代码大小不能超过 32k ，对于超长的代码可以将其置于单独的存储过程，然后在触发器中使用 call 调用该过程。
触发器代码只能包含 DQL ， DML ，而不能包含 DDL 以及事务控制语言 ( COMMIT,ROLLBACK,SAVEPOINT ) 。
d . 触发频率
说明触发器内定义的动作被执行的次数。即语句级 ( STATEMENT ) 触发器和行级 ( ROW ) 触发器。
语句级 ( STATEMENT ) 触发器：是指当某触发事件发生时，该触发器只执行一次。
行级 ( ROW ) 触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。
3. 触发器的用途
控制 DDL 语句的行为，如通过更改、创建或重命名对象
控制 DML 语句的行为，如插入、更新和删除
实施参照完整性、复杂业务规则和安全性策略
在修改视图中的数据时控制和重定向 DML 语句
通过创建透明日志来审核系统访问和行为的信息
二、创建 DML 触发器语法描述
1. 创建触发器的语法
CREATE [OR REPLACE] TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF}
{ INSERT | DELETE | UPDATE [OF column [, column …]]} -- 定义触发类型，即那一种或多种 DML 以及特定的列
ON {[schema.] table_name | [schema.] view_name} -- 特定的触发对象，表或视图
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ] -- 定义触发器为行级触发器
[WHEN condition]
BEGIN
trigger_body ;
END ;
BEFORE | AFTER | INSTEAD OF
BEFORE 指在执行 DML 之前触发触发器， AFTER 则是指在 DML 执行之后触发触发器
INSTEAD OF 触发器只针对视图和对象视图建立，而不能对表、模式和数据库建立 INSTEAD OF 触发器
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
说明相关名称，在行触发器的 PL / SQL 块和 WHEN 子句中可以使用相关名称参照当前的新 , 旧列值 , 默认的相关名称分别为 OLD 和 NEW 。
触发器的 PL / SQL 块中应用相关名称时，必须在它们之前加冒号 (:) ，但在 WHEN 子句中则不能加冒号。
[FOR EACH ROW ]
定义触发器为行级触发器。
行级触发器和语句级触发器的区别表现在：一个 DML 语句可能操纵多行，也可能操纵一行，使用行级触发器，不论是一行还是多
行数据被操纵，行触发器为该 DML 的每一行触发一次触发器操作。语句级触发器将整个语句操作作为触发事件，不论该语句影响
了多少行，仅仅触发一次触发器。
当省略 FOR EACH ROW 选项时， BEFORE 和 AFTER 触发器为语句触发器，而 INSTEAD OF 触发器则为行触发器。
[WHEN condition]
触发条件，当条件为 TRUE 时，触发器代码才会被执行，对于 DML 触发器，仅仅允许在行级触发器上指定触发条件。
condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用 PL / SQL 函数。
WHEN 子句可通过引用 new 或 old 伪记录、一个组件选择符和一个列名来访问伪字段。
WHEN 子句不能用在 INSTEAD OF 行触发器和其它类型的触发器中。
2.DML 触发器的触发顺序
a . 在单行数据上的触发顺序 ( 触发代码仅被执行一次 )
BEFORE 语句级触发器
BEFORE 行级触发器
AFTER 行级触发器
AFTER 语句级触发器
b . 在多行数据上的触发顺序 ( 语句级触发器仅被执行一次，行级触发器在每个作业行上被执行一次 )
BEFORE 语句级触发器
BEFORE 行级触发器
AFTER 行级触发器
BEFORE 行级触发器
AFTER 行级触发器
AFTER 语句级触发器
3. 触发器中的条件谓词
ORACLE 提供三个参数 INSERTING , UPDATING , DELETING 用于判断触发了哪些操作。
INSERTING ：如果触发语句是 INSERT 语句，则为 TRUE, 否则为 FALSE 。
UPDATING ：如果触发语句是 UPDATE 语句，则为 TRUE, 否则为 FALSE 。
DELETING ：如果触发语句是 DELETE 语句，则为 TRUE, 否则为 FALSE 。
4.NEW 、 OLD 限定符的使用
使用被插入、更新或删除的记录中的列值，可以使用 NEW 和 OLD 限定符来表示
: old 修饰符访问操作完成前列的值
: new 修饰符访问操作完成后列的值
限定符 INSERT 操作 UPDATE 操作 DELETE 操作
--------- -------------- ----------- ----------
OLD NULL 有效 有效
NEW 有效 有效 NULL
三、创建 DML 触发器
1. 创建 BEFORE 语句级触发器
sys@ORCL > drop user scott cascade ; -- 删除 scott 方案
sys@ORCL > start $ORACLE_HOME / rdbms / admin / utlsampl . sql -- 重建 scott 方案
sys@ORCL > grant dba to scott ; -- 授予 Scott DBA 角色
scott@ORCL > create table emp_check ( oper varchar2 ( 30 ), upd_date date ); -- 创建表存放 emp 表的更新记录操作的跟踪
CREATE OR REPLACE TRIGGER tr_before_update_emp -- 创建 update 触发器
BEFORE UPDATE ON emp
-- FOR EACH ROW
BEGIN
INSERT INTO emp_check
Values
( 'Before update, statement level' , sysdate );
END ;
scott@ORCL > select * from emp_check ; -- 未执行 update 前，跟踪表记录为空
no rows selected
scott@ORCL > update emp set sal = sal + 100 where deptno = 20 ; -- 更新了四条记录
4 rows updated .
scott@ORCL > select * from emp_check ; -- 跟踪表表插入了一条跟踪记录
OPER UPD_DATE
------------------------------ ---------
Before update , statement level 24 - DEC - 10
scott@ORCL > update emp set sal = sal + 200 where empno = 7369 ; -- 更新了一条记录，跟踪表再次插入一条新记录
1 row updated .
scott@ORCL > select * from emp_check ;
OPER UPD_DATE
------------------------------ ---------
Before update , statement level 24 - DEC - 10
Before update , statement level 24 - DEC - 10
2. 创建 BEFORE 行级触发器
使用上面的代码来创建行级触发器，与之不同的是将上面的代码中 "-- FOR EACH ROW" 的 "--" 删除，则创建的触发器即为行级触发器
代码省略
scott@ORCL > update emp set sal = sal + 200 where deptno = 20 ; -- 再次更新 deptno 为的记录，且记录总数为四条
4 rows updated .
scott@ORCL > select * from emp_check ; -- 表 emp_check 中增加四条，即为 update 的每一行增加一条记录
OPER UPD_DATE
------------------------------ ---------
Before update , statement level 24 - DEC - 10
Before update , statement level 24 - DEC - 10
Before update , statement level 24 - DEC - 10
Before update , statement level 24 - DEC - 10
Before update , statement level 24 - DEC - 10
Before update , statement level 24 - DEC - 10
3. 创建 AFTER 语句级触发器
CREATE TABLE audit_table_emp -- 创建一张表 audit_table_emp 存放 emp 表上 DML 操作的次数
(
name VARCHAR2 ( 20 ),
ins INT ,
upd INT ,
del INT ,
starttime DATE ,
endtime DATE
);
CREATE OR REPLACE TRIGGER tr_audit_emp
AFTER INSERT OR UPDATE OR DELETE ON emp
DECLARE
v_temp INT ;
BEGIN
SELECT COUNT (*) INTO v_temp FROM audit_table_emp WHERE name = 'EMP' ;
IF v_temp = 0 THEN
INSERT INTO audit_table_emp VALUES ( 'EMP' , 0 , 0 , 0 , SYSDATE , NULL);
END IF ;
CASE
WHEN INSERTING THEN -- 注意此例中条件谓词的使用 INSERTING 、 UPDATING 、 DELETING
UPDATE audit_table_emp SET ins = ins + 1 , endtime = SYSDATE WHERE name = 'EMP' ;
WHEN UPDATING THEN
UPDATE audit_table_emp SET upd = upd + 1 , endtime = SYSDATE WHERE name = 'EMP' ;
WHEN DELETING THEN
UPDATE audit_table_emp SET del = del + 1 , endtime = SYSDATE WHERE name = 'EMP' ;
END CASE ;
END ;
scott@ORCL > update emp set sal = sal + 200 where empno = 7788 ;
scott@ORCL > update emp set sal = sal + 200 where ename = 'SMITH' ;
scott@ORCL > delete from emp where empno = 7788 ;
scott@ORCL > select * from audit_table_emp ; -- 两次更新及一次被记录到表中
NAME INS UPD DEL STARTTIME ENDTIME
-------------------- ---------- ---------- ---------- --------- ---------
EMP 0 2 1 24 - DEC - 10 24 - DEC - 10
scott@ORCL > update emp set sal = sal + 100 where deptno = 10 ;
3 rows updated . -- 更新了行，当 audit_table_emp 表中仅仅记录一次， UPD 的值增加到
scott@ORCL > select * from audit_table_emp ;
NAME INS UPD DEL STARTTIME ENDTIME
-------------------- ---------- ---------- ---------- --------- ---------
EMP 0 3 1 24 - DEC - 10 24 - DEC - 10
4. 创建 AFTER 行级触发器
CREATE TABLE audit_emp_change -- 创建 audit_emp_change 存放 emp 表 sal 列被更新前后的值
(
name VARCHAR2 ( 10 ),
oldsal NUMBER ( 6 , 2 ),
newsal NUMBER ( 6 , 2 ),
time DATE
);
CREATE OR REPLACE TRIGGER tr_sal_change
AFTER UPDATE OF sal ON emp -- 注意 update 触发器中使用了 OF 关键字，当 sal 列发生变化时， tr_sal_change 被触发
FOR EACH ROW -- 使用行级触发器
--WHEN （ old.job='CLERK')
DECLARE
v_temp INT ;
BEGIN
SELECT COUNT (*) INTO v_temp FROM audit_emp_change WHERE name = : old . ename ; -- 注意 OLD 与 NEW 的使用
IF v_temp = 0 THEN
INSERT INTO audit_emp_change VALUES (: old . ename , : old . sal , : new . sal , SYSDATE );
ELSE
UPDATE audit_emp_change SET oldsal = : old . sal , newsal = : new . sal , time = SYSDATE WHERE name = : old . ename ;
END IF ;
END ;
scott@ORCL > update emp set sal = sal - 100 where empno = 7369 ; -- 更新一行
scott@ORCL > select * from audit_emp_change ; --audit_emp_change 表中记录了一行
NAME OLDSAL NEWSAL TIME
---------- ---------- ---------- ---------
SMITH 1400 1300 24 - DEC - 10
scott@ORCL > delete from audit_emp_change ; -- 删除之前的记录
scott@ORCL > update emp set sal = sal + 200 where deptno = 10 ; -- 更新了三行
3 rows updated .
scott@ORCL > select * from audit_emp_change ; --audit_emp_change 表中记录了三行
NAME OLDSAL NEWSAL TIME
---------- ---------- ---------- ---------
CLARK 2550 2750 24 - DEC - 10
KING 5100 5300 24 - DEC - 10
MILLER 1400 1600 24 - DEC - 10
5. 限制行级触发器
限制行级触发器是通过添加 [WHEN condition] 判断条件，来对满足特定 condition 的记录触发触发器。
对于 DML 触发器而言，仅仅允许在行级触发器上指定触发条件。
在上面第 4 小点创建 AFTER 行级触发器的代码中，这一行 "--WHEN （ old.job='CLERK')" 去掉 "--" ，触发器变为具有限定条件的行级
触发器，则对特定的条件，即 job = 'CLERK' 的记录在更新其 sal 时才会被触发。演示省略。
6. 创建 DML 触发器的注意事项
DML 触发器中不能包含对基表的 DQL 查询操作
CREATE OR REPLACE TRIGGER tr_emp_sal
BEFORE UPDATE OF sal ON emp -- 注意 update 触发器中使用了 OF 关键字，当 sal 列发生变化时， tr_emp_sal 被触发
FOR EACH ROW -- 使用行级触发器
DECLARE
maxsal NUMBER ( 6 , 2 );
BEGIN
SELECT MAX ( sal ) INTO maxsal FROM emp ;
IF : new . sal > maxsal THEN
RAISE_APPLICATION_ERROR (- 20004 , 'Beyond the highest salary' );
END IF ;
END ;
scott@ORCL > update emp set sal = 5000 where empno = 7369 ;
update emp set sal = 5000 where empno = 7369
*
ERROR at line 1 :
ORA - 04091 : table SCOTT . EMP is mutating , trigger / function may not see it
ORA - 06512 : at "SCOTT.TR_EMP_SAL" , line 4
ORA - 04088 : error during execution of trigger 'SCOTT.TR_EMP_SAL'
7. 使用 DML 触发器实现数据完整性，参照完整性
对于复杂的数据完整性，参照完整性，可以通过 DML 触发器来完成普通约束所不能完成的任务
CREATE OR REPLACE TRIGGER tr_del_upd_deptno
AFTER DELETE OR UPDATE OF deptno ON dept
FOR EACH ROW
BEGIN
IF ( UPDATING AND : old . deptno <>: new . deptno ) THEN
UPDATE emp SET deptno =: new . deptno WHERE deptno =: old . deptno ;
END IF ;
IF DELETING THEN
DELETE FROM emp WHERE deptno =: old . deptno ;
END IF ;
END ;
scott@ORCL > update dept set deptno = 50 where deptno = 10 ;
1 row updated .
scott@ORCL > select * from emp where deptno = 10 ;
no rows selected
scott@ORCL > select * from emp where deptno = 50 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09 - JUN - 81 2750 50
7839 KING PRESIDENT 17 - NOV - 81 5300 50
7934 MILLER CLERK 7782 23 - JAN - 82 1600 50
四、管理触发器
1. 查看系统中特定对象上的触发器
scott@ORCL > select trigger_name , status from user_triggers
2 where table_name = 'EMP' ;
TRIGGER_NAME STATUS
------------------------------ --------
TR_BEFORE_UPDATE_EMP ENABLED
TR_AUDIT_EMP ENABLED
TR_SEC_EMP ENABLED
TR_SAL_CHANGE ENABLED
TR_EMP_SAL ENABLED
2. 查看触发器的源代码
scott@ORCL > col text format a65
scott@ORCL > select line , text from user_source where name = 'TR_DEL_UPD_DEPTNO' ;
LINE TEXT
---------- -----------------------------------------------------------------
1 TRIGGER tr_del_upd_deptno
2 AFTER DELETE OR UPDATE OF deptno ON dept
3 FOR EACH ROW
4 BEGIN
5 IF ( UPDATING AND : old . deptno <>: new . deptno ) THEN
6 UPDATE emp SET deptno =: new . deptno WHERE deptno =: old . deptno ;
7 END IF ;
8
9 IF DELETING THEN
10 DELETE FROM emp WHERE deptno =: old . deptno ;
11 END IF ;
12 END ;
3. 禁用触发器
当触发器被禁用后，则表上的 DML 操作将不会触发该触发器，直到该触发器被解除禁用 ( alter trigger trigger_name disable )
scott@ORCL > alter trigger tr_emp_sal disable ;
4. 启用触发器
被禁用的触发器可以被解除禁用 ( alter trigger trigger_name enable )
scott@ORCL > alter trigger tr_emp_sal enable ;
5. 禁用、启用表上的所有触发器
scott@ORCL > alter table emp disable all triggers ;
scott@ORCL > alter table emp enable all triggers ;
6. 重新编译触发器
scott@ORCL > alter trigger tr_emp_sal compile ;
7. 删除触发器
scott@ORCL > drop trigger tr_emp_sal ;
五、更多参考
有关 SQL 请参考
SQL 基础--> ROLLUP 与CUBE 运算符实现数据汇总
SQL 基础--> 层次化查询(START BY ... CONNECT BY PRIOR)
有关 PL/SQL 请参考
