把触发器谈透

把触发器说透

触发器是许多关系数据库系统都提供的一项技术。在ORACLE系统里，触发器类似过程和函数，都有声明，执行和异常处理过程的PL/SQL块。

8.1?触发器类型

??? 触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。ORACLE将触发器的功能扩展到了触发ORACLE，如数据库的启动与关闭等。所以触发器常用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或用来监视对数据库的各种操作，实现审计的功能。

?

8.1.1 DML触发器

??? ORACLE可以在DML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。

?

8.1.2 替代触发器

??? 由于在ORACLE里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。它就是ORACLE 8专门为进行视图操作的一种处理方法。

?

8.1.3 系统触发器

ORACLE 8i 提供了第三种类型的触发器叫系统触发器。它可以在ORACLE数据库系统的事件中进行触发，如ORACLE系统的启动与关闭等。

?

触发器组成:?

l???????? 触发事件：引起触发器被触发的事件。 例如：DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。

l???????? 触发时间：即该TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER 的操作顺序。

l???????? 触发操作：即该TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。 例如：PL/SQL 块。

l???????? 触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作。

l???????? 触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作。

l???????? 触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。

语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；

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

编写触发器时，需要注意以下几点：

l???????? 触发器不接受参数。

l???????? 一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。

l???????? 在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。

l????????触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用。

l???????? 在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）。

l???????? 触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了。

l???????? 在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。

l???????? 在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能向表中的任何long和blob列。

l???????? 不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和作用有较大区别。
?

8.2?创建触发器

创建触发器的一般语法是:

?

特性

INSERT

UPDATE

DELETE

OLD

NULL

实际值

实际值

NEW

实际值

实际值

NULL

?

例1: 建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。

?

CREATE?TABLE?emp_his?AS?SELECT?*?FROM?EMP?WHERE?1=2;?
CREATE?OR?REPLACE?TRIGGER?tr_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;
DELETE?emp?WHERE?empno=7788;
DROP?TABLE?emp_his;
DROP?TRIGGER?del_emp;

?

例2：限制对Departments表修改（包括INSERT,DELETE,UPDATE）的时间范围，即不允许在非工作时间修改departments表。

?

CREATE?OR?REPLACE?TRIGGER?tr_dept_time
BEFORE?INSERT?OR?DELETE?OR?UPDATE?
ON?departments
BEGIN
?IF?(TO_CHAR(sysdate,'DAY')?IN?('星期六',?'星期日'))?OR?(TO_CHAR(sysdate,?'HH24:MI')?NOT?BETWEEN?'08:30'?AND?'18:00')?THEN
?????RAISE_APPLICATION_ERROR(-20001,?'不是上班时间，不能修改departments表');
?END?IF;
END;

?

例3：限定只对部门号为80的记录进行行触发器操作。

?

CREATE?OR?REPLACE?TRIGGER?tr_emp_sal_comm
BEFORE?UPDATE?OF?salary,?commission_pct
???????OR?DELETE
ON?HR.employees
FOR?EACH?ROW
WHEN?(old.department_id?=?80)
BEGIN
?CASE
?????WHEN?UPDATING?('salary')?THEN
????????IF?:NEW.salary?<?:old.salary?THEN

???????????RAISE_APPLICATION_ERROR(-20001,?'部门80的人员的工资不能降');
????????END?IF;
?????WHEN?UPDATING?('commission_pct')?THEN

????????IF?:NEW.commission_pct?<?:old.commission_pct?THEN
???????????RAISE_APPLICATION_ERROR(-20002,?'部门80的人员的奖金不能降');
????????END?IF;
?????WHEN?DELETING?THEN
??????????RAISE_APPLICATION_ERROR(-20003,?'不能删除部门80的人员记录');
?????END?CASE;
END;?

/*
实例：
UPDATE?employees?SET?salary?=?8000?WHERE?employee_id?=?177;
DELETE?FROM?employees?WHERE?employee_id?in?(177,170);
*/

?

?

例4：利用行触发器实现级联更新。在修改了主表regions中的region_id之后（AFTER），级联的、自动的更新子表countries表中原来在该地区的国家的region_id。

?

?

CREATE?OR?REPLACE?TRIGGER?tr_reg_cou
AFTER?update?OF?region_id
ON?regions
FOR?EACH?ROW
BEGIN
?DBMS_OUTPUT.PUT_LINE('旧的region_id值是'||:old.region_id
??????????????????||'、新的region_id值是'||:new.region_id);
?UPDATE?countries?SET?region_id?=?:new.region_id
?WHERE?region_id?=?:old.region_id;
END;

例5：在触发器中调用过程。

?

CREATE?OR?REPLACE?PROCEDURE?add_job_history
?(?p_emp_id??????????job_history.employee_id%type
???,?p_start_date??????job_history.start_date%type
??,?p_end_date????????job_history.end_date%type
???,?p_job_id??????????job_history.job_id%type
???,?p_department_id???job_history.department_id%type
???)
IS
BEGIN
?INSERT?INTO?job_history?(employee_id,?start_date,?end_date,
???????????????????????????job_id,?department_id)
??VALUES(p_emp_id,?p_start_date,?p_end_date,?p_job_id,?p_department_id);
END?add_job_history;

--创建触发器调用存储过程...
CREATE?OR?REPLACE?TRIGGER?update_job_history
?AFTER?UPDATE?OF?job_id,?department_id?ON?employees
?FOR?EACH?ROW
BEGIN
?add_job_history(:old.employee_id,?:old.hire_date,?sysdate,
??????????????????:old.job_id,?:old.department_id);
END;

?

8.2.3 创建替代(INSTEAD OF)触发器

?

创建触发器的一般语法是:

?

CREATE?[OR?REPLACE]?TRIGGER?trigger_name
INSTEAD?OF
{INSERT?|?DELETE?|?UPDATE?[OF?column?[,?column?…]]}
[OR?{INSERT?|?DELETE?|?UPDATE?[OF?column?[,?column?…]]}...]
ON?[schema.]?view_name?--只能定义在视图上
[REFERENCING?{OLD?[AS]?old?|?NEW?[AS]?new|?PARENT?as?parent}]
[FOR?EACH?ROW?]?--因为INSTEAD?OF触发器只能在行级上触发,所以没有必要指定
[WHEN?condition]
PL/SQL_block?|?CALL?procedure_name;

?

其中：

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

??????????? FOR EACH ROW选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，而INSTEAD OF 触发器则为行触发器。

??????????? REFERENCING 子句说明相关名称，在行触发器的PL/SQL块和WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。

WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL 函数。WHEN 子句指定的触发约束条件只能用在BEFORE 和AFTER 行触发器中，不能用在INSTEAD OF 行触发器和其它类型的触发器中。

?

??? INSTEAD_OF 用于对视图的DML触发，由于视图有可能是由多个表进行联结(join)而成，因而并非是所有的联结都是可更新的。但可以按照所需的方式执行更新，例如下面情况：

例1：

?

CREATE?OR?REPLACE?VIEW?emp_view?AS?
SELECT?deptno,?count(*)?total_employeer,?sum(sal)?total_salary?
FROM?emp?GROUP?BY?deptno;

?

在此视图中直接删除是非法：

?

SQL>DELETE?FROM?emp_view?WHERE?deptno=10;
DELETE?FROM?emp_view?WHERE?deptno=10

?

ERROR 位于第 1 行:

ORA-01732: 此视图的数据操纵操作非法

?

但是我们可以创建INSTEAD_OF触发器来为 DELETE 操作执行所需的处理，即删除EMP表中所有基准行：

?

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;?

DROP?TRIGGER?emp_view_delete;

DROP?VIEW?emp_view;?

?

例2：创建复杂视图，针对INSERT操作创建INSTEAD OF触发器，向复杂视图插入数据。

l???????? 创建视图：

?

CREATE?OR?color:

查看更多 下一篇