PL/SQL --> DML 触发器

--=======================

-- 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 基础--> 子查询

SQL 基础-->多表查询

SQL基础-->分组与分组函数

SQL 基础-->常用函数

SQL 基础--> ROLLUP与CUBE运算符实现数据汇总

SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)