触发器是由一个事件来启动运行。 即触发器是当某个事件发生时自动地隐式运行。并且,触发器不能接收参数。所以 运行触发器就叫触发或点火(firing) )。ORACLE 事件指的是对数据库的表进行的INSERT 、UPDATE 及 及 DELETE 操作或对视图进行类似的操作。
触发器类型有三种
1)DML触发器
ORACLE 可以在 DML 语句进行触发,可以在 DML 操作前或操作后进行触发,并且 可以对每个行或语句操作上进行触发。
2)替代触发器
由于在 ORACLE 里,不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。
3)系统触发器
它可以在 ORACLE 数据库系统的事件中进行触发,如 ORACLE 系统的启动与关闭等。
触发器组成
触发事件:即在何种情况下触发 TRIGGER;例如:INSERT, UPDATE, DELETE。
触发时间:即该 TRIGGER 是在触发事件发生之前(BEFORE)还是之后(AFTER)触发,也就是触发事件和该 TRIGGER 的操作顺序。
触发器本身:即该 TRIGGER 被触发之后的目的和意图,正是触发器本身要做的事情。 例如:PL/sql 块。
触发频率:说明触发器内定义的动作被执行的次数。即 语句级(STATEMENT) 触发器和行级(ROW) 触发器。
语句级(STATEMENT)触发器:是指当某触发事件发生时,该触发器只执行一次;
行级(ROW)触发器:是指当某触发事件发生时,对受到该操作影响的每一行数据,触发器都单独执行一次。
创建一般触发器
CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER } {INSERT | DELETE | UPDATE [OF column [,column …]]} ON [schema.] table_name [FOR EACH ROW ] [WHEN condition] trigger_body;
其中:
BEFORE 和 AFTER 指出触发器的触发时序分别为前触发和后触发方式,前触发是在执行触发事件之前触发当前所创建的触发器,后触发是在执行触发事件之后触发当前所创建的触发器。
FOR EACH ROW 选项说明触发器为行触发器。 行触发器和语句触发器的区别表现在:行触发器要求当一个 DML 语句操做影响数据库中的多行数据时,对于其中的每个数据行,只要它们符合触发约束条件,均激活一次触发器;而语句触发器将整个语句操作作为触发事件,当它符合约束条件时,激活一次触发器。 当省略 FOR EACH ROW 选项时,BEFORE 和 AFTER 触发器为 语句触发器,而 INSTEAD OF 触发器则为行触发器。
WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时,其中必须包含相关名称,而不能包含查询语句,也不能调用 PL/sql 函数。WHEN 子句指定的触发约束条件只能用在 BEFORE 和 AFTER 行触发器中,不能用在 INSTEAD OF 行触发器和其它类型的触发器中。
触发器触发次序
1. 执行 BEFORE 语句级触发器;2. 对与受语句影响的每一行:
a.执行 BEFORE 行级触发器
b.执行 DML 语句
c.执行 AFTER 行级触发器
3. 执行 AFTER 语句级触发器
创建 DML触发器
触发器名可以和表或过程有相同的名字,但在一个模式中触发器名不能相同。
--编写一个触发器,在对my_emp记录进行删除的时候,在my_emp_bak表中备份对应的记录 SET SERVEROUTPUT ON --新建一个my_emp表 CREATE TABLE MY_EMP AS SELECT E.EMPLOYEE_ID,E.SALARY FROM EMPLOYEES E; --新建一个备份表my_emp_bak表 CREATE TABLE MY_EMP_bak AS SELECT E.EMPLOYEE_ID,E.SALARY FROM EMPLOYEES E WHERE 1=2; --创建一个触发器 CREATE OR REPLACE TRIGGER DELETE_EMP_TRIGGER BEFORE --触发时间 DELETE ON MY_EMP FOR EACH ROW BEGIN INSERT INTO MY_EMP_BAK VALUES(:OLD.EMPLOYEE_ID,:OLD.SALARY); END; /
其中:
:NEW 修饰符访问操作完成后列的值。
:OLD 修饰符访问操作完成前列的值。
触发器的限制
CREATE TRIGGER 语句文本的字符长度不能超过 32KB;
触发器体内的 SELECT 语句只能为 SELECT … INTO … 结构,或者为定义游标所使用的 SELECT 语句。
触发器中不能使用数据库事务控制语句 COMMIT; ROLLBACK,SVAEPOINT 语句;
创建替代(INSTEAD OF)触发器
语法:
CREATE [OR REPLACE] TRIGGER trigger_name INSTEAD OF {INSERT | DELETE | UPDATE [OF column [,column …]]} ON [schema.] view_name [FOR EACH ROW ] [WHEN condition] trigger_body;其中:
BEFORE 和 AFTER 指出触发器的触发时序分别为前触发和后触发方式,前触发是在执行触发事件之前触发当前所创建的触发器,后触发是在执行触发事件之后触发当前所创建的触发器。
INSTEAD OF 选项使 ORACLE 激活触发器,而不执行触发事件。 只能对视图和对象视图建立 INSTEAD OF触发器,而不能对表、模式和数据库建立 INSTEAD OF 触发器。
FOR EACH ROW 选项说明触发器为行触发器。行触发器和语句触发器的区别表现在:行触发器要求当一个 DML 语句操做影响数据库中的多行数据时,对于其中的每个数据行,只要它们符合触发约束条件,均激活一次触发器;而语句触发器将整个语句操作作为触发事件,当它符合约束条件时,激活一次触发器。当省略 FOR EACH ROW 选项时,BEFORE 和 AFTER 触发器为语句触发器,而 INSTEAD OF 触发器则为行触发器。
WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时,其中必须包含相关名称,而不能包含查询语句,也不能调用 PL/sql 函数。WHEN 子句指定的触发约束条件只能用在 BEFORE 和 AFTER 行触发器中,不能用在 INSTEAD OF 行触发器和其它类型的触发器中。
INSTEAD_OF 用于对视图的 DML 触发,由于视图有可能是由多个表进行联结(join)而成,因而并非是所有的联结都是可更新的。但可以按照所需的方式执行更新,例如下面情况:
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 ;