ORACLE 触发器

触发器

• 触发器是在数据库里的独立的对象存储
• 触发器是由一个事件来启动运行
• 即触发器是当某个事件发生时自动地隐式运行

触发器类型

• DML触发器：ORACLE可以在DML语句进行触发
• 替代触发器：ORACLE专门为进行视图操作的一种处理方法
• 系统触发器：在ORACLE数据库系统的事件中进行触发

触发器的组成主要有

• 触发事件：何种情况下触发TRIGGER
• 触发时间：即该TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发
• 触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器

1. 语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；
2. 行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次

• 触发器本身：即该TRIGGER 被触发之后的目的和意图

创建触发器
创建触发器的一般语法是 ：
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
ON [schema.] table_name
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
trigger_body;

• FOR EACH ROW选项说明触发器为行触发器
• 行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；
• 语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器
• 当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，而INSTEAD OF 触发器则为行触发器
• REFERENCING 子句说明相关名称，在行触发器的PL/SQL块和WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW

每张表最多可创建12种触发器：
BEFORE INSERT
BEFORE INSERT FOR EACH ROW
AFTER INSERT
AFTER INSERT FOR EACH ROW
BEFORE UPDATE
BEFORE UPDATE FOR EACH ROW
AFTER UPDATE
AFTER UPDATE FOR EACH ROW
BEFORE DELETE
BEFORE DELETE FOR EACH ROW
AFTER DELETE
AFTER DELETE FOR EACH ROW
触发器执行顺序：

• 执行 BEFORE语句级触发器
• 对与受语句影响的每一行：

1. 执行 BEFORE行级触发器
2. 执行 DML语句
3. 执行 AFTER行级触发器

• 执行 AFTER语句级触发器

例：

CREATE OR REPLACE TRIGGER del_emp 
BEFORE DELETE ON scott.emp FOR EACH ROW
BEGIN
-- 将修改前数据插入到日志记录表 del_emp ,以供监督使用。
INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
VALUES( :old.deptno, :old.empno, :old.ename , :old.job,
:old.mgr, :old.sal, :old.comm, :old.hiredate );
END;

触发器的限制

• CREATE TRIGGER语句文本的字符长度不能超过32KB
• 触发器体内的SELECT 语句只能为SELECT … INTO …结构，或者为定义游标所使用的SELECT 语句
• 触发器中不能使用数据库事务控制语句 COMMIT， ROLLBACK, SVAEPOINT 语句
• 由触发器所调用的过程或函数也不能使用数据库事务控制语句
• 触发器中不能使用LONG, LONG RAW 类型
• 触发器内可以参照LOB类型列的列值，但不能通过 :NEW 修改LOB列中的数据

当触发器被触发时，要使用被插入、更新或删除的记录中的列值，有时要使用操作前、后列的值 ，可以使用：

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

例：

CREATE OR REPLACE TRIGGER del_emp
BEFORE update ON scott.emp
REFERENCING new AS nn old AS oo
FOR EACH ROW
WHEN (nn.sal > 2000)
BEGIN
dbms_output.put_line(:nn.sal||'------'||:oo.sal);
END;

触发器中的谓词

• INSERTING：如果触发语句是INSERT，则为TRUE，否则为FALSE
• UPDATING：如果触发语句是UPDATE，则为TRUE，否则为FALSE
• DELETING：如果触发语句是DELETE，则为TRUE，否则为FALSE

例：

CREATE OR REPLACE TRIGGER check_emp
　　BEFORE update OR insert OR delete ON scott.emp
　　REFERENCING new AS nn old AS oo
　　FOR EACH ROW
　　WHEN (nn.sal > 2000)
BEGIN
　　IF INSERTING THEN
　　　　dbms_output.put_line('THE OPERATION IS INSERT');
　　ELSIF UPDATING THEN
　　　　dbms_output.put_line('THE OPERATION IS UPDATE');
　　ELSIF DELETING THEN
　　　　dbms_output.put_line('THE OPERATION IS DELETE');
　　ELSE
　　　　dbms_output.put_line('OTHERS OPERATION');
　　END IF;
END;

创建替代触发器
CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT | DELETE | UPDATE [OF column [, column …]]}
ON [schema.] view_name
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
trigger_body;

• INSTEAD OF 选项使ORACLE激活触发器，而不执行触发事件
• INSTEAD OF只能对视图和对象视图建立INSTEAD OF触发器，而不能对表、模式和数据库建立INSTEAD OF 触发器

例

CREATE OR REPLACE TRIGGER emp_view_delete
　　INSTEAD OF DELETE ON emp_view FOR EACH ROW
BEGIN
　　DELETE FROM emp WHERE deptno= :old.deptno;
END emp_view_delete;

DELETE FROM emp_view WHERE deptno=10;

创建系统触发器
CREATE OR REPLACE TRIGGER [sachema.] trigger_name
{BEFORE|AFTER}
{ddl_event_list | database_event_list}
ON { DATABASE | [schema.] SCHEMA }
[WHEN_clause]
trigger_body;
其中:
ddl_event_list为一个或多个DDL事件，事件间用 OR 分开
database_event_list为一个或多个数据库事件，事件间用 OR 分开

系统触发器的种类和事件出现的时机

删除触发器使用下面的语句：
DROP TRIGGER trigger_name;

相关数据字典：
USER_TRIGGERS、ALL_TRIGGERS、DBA_TRIGGERS
例：

SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT,
　　TABLE_OWNER, BASE_OBJECT_TYPE, REFERENCING_NAMES,
　　STATUS, ACTION_TYPE
　　FROM user_triggers;