Oracle触发器原理、创建、修改、删除
本篇主要内容如下：
8.1 触发器类型
8.1.1 DML触发器
8.1.2 替代触发器
8.1.3 系统触发器
8.2 创建触发器
8.2.1 触发器触发次序
8.2.2 创建DML触发器
8.2.3 创建替代(INSTEAD OF)触发器
8.2.3 创建系统事件触发器
8.2.4 系统触发器事件属性
8.2.5 使⽤触发器谓词
8.2.6 重新编译触发器
8.3 删除和使⽤触发器
8.4 触发器和数据字典
8.5 触发器的应⽤举例
8.6 触发器的查看
8.7 触发器注意点
触发器是许多关系数据库系统都提供的⼀项技术。在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)触发器和⾏级(RO
W)触发器。
语句级(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 创建触发器
创建触发器的⼀般语法是:
CREATE[OR REPLACE]TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT|DELETE|UPDATE[OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON[schema.]table_name |[schema.]view_name
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
PL/SQL_BLOCK | CALL procedure_name;
其中：
BEFORE 和AFTER指出触发器的触发时序分别为前触发和后触发⽅式，前触发是在执⾏触发事件之前触发当前所创建的触发器，后触发是在执⾏触发事件之后触发当前所创建的触发器。
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 ⾏触发器和其它类型的触发器中。
当⼀个基表被修改( INSERT, UPDATE, DELETE)时要执⾏的存储过程，执⾏时根据其所依附的基表改动⽽⾃动触发，因此与应⽤程序⽆关，⽤数据库触发器可以保证数据的⼀致性和完整性。
每张表最多可建⽴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
8.2.1 触发器触发次序
1. 执⾏ BEFORE语句级触发器;
2. 对与受语句影响的每⼀⾏：
l 执⾏ BEFORE⾏级触发器
l 执⾏ DML语句
l 执⾏ AFTER⾏级触发器
3. 执⾏ AFTER语句级触发器
8.2.2 创建DML触发器
触发器名与过程名和包的名字不⼀样，它是单独的名字空间，因⽽触发器名可以和表或过程有相同的名字，但在⼀个模式中触发器名不能相同。
DML触发器的限制
l CREATE TRIGGER语句⽂本的字符长度不能超过32KB；
l 触发器体内的SELECT 语句只能为SELECT … INTO …结构，或者为定义游标所使⽤的SELECT 语句。
l 触发器中不能使⽤数据库事务控制语句 COMMIT; ROLLBACK, SVAEPOINT 语句；
l 由触发器所调⽤的过程或函数也不能使⽤数据库事务控制语句；
l 触发器中不能使⽤LONG, LONG RAW 类型；
l 触发器内可以参照LOB 类型列的列值，但不能通过 :NEW 修改LOB列中的数据；
DML触发器基本要点
l 触发时机：指定触发器的触发时间。如果指定为BEFORE，则表⽰在执⾏DML操作之前触发，以便防⽌某些错误操作发⽣或实现某些业务规则；如果指定为AFTER，则表⽰在执⾏DML操作之后触发，以便记录该操作或做某些事后处理。
l 触发事件：引起触发器被触发的事件，即DML操作（INSERT、UPDATE、DELETE）。既可以是单个触发事件，也可以是多个触发事件的组合（只能使⽤OR逻辑组合，不能使⽤AND逻辑组合）。
l 条件谓词：当在触发器中包含多个触发事件（INSERT、UPDATE、DELETE）的组合时，为了分别针对不同的事件进⾏不同的处理，需要使⽤ORACLE提供的如下条件谓词。
1）。INSERTING：当触发事件是INSERT时，取值为TRUE，否则为FALSE。
2）。UPDATING [（column_1,column_2,…,column_x）]：当触发事件是UPDATE 时，如果修改了column_x列，则取值为TRUE，否则为FALSE。其中column_x是可选的。
3）。DELETING：当触发事件是DELETE时，则取值为TRUE，否则为FALSE。
解发对象：指定触发器是创建在哪个表、视图上。
l 触发类型：是语句级还是⾏级触发器。
l 触发条件：由WHEN⼦句指定⼀个逻辑表达式，只允许在⾏级触发器上指定触发条件，指定UPDATING后⾯的列的列表。
问题：当触发器被触发时，要使⽤被插⼊、更新或删除的记录中的列值，有时要使⽤操作前、后列的值.
实现: :NEW 修饰符访问操作完成后列的值
:OLD 修饰符访问操作完成前列的值
特性INSERTUPDATEDELETE
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 --指定触发时机为删除操作前触发 p FOR EACH ROW --说明创建的是⾏级触发器 BEGIN --将修改前数据插⼊到⽇志记录表 del_emp ，以供监督使⽤。 INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
VALUES( :old.deptno, :pno, :ame , :old.job,:, :old.sal, :oldm, :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
oracle游标的使用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
ployees
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 :NEWmission_pct < :oldmission_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值是'||:ion_id
||'、新的region_id值是'||:ion_id);
UPDATE countries SET region_id = :ion_id
WHERE region_id = :ion_id;
END;
例5：在触发器中调⽤过程。
CREATE OR REPLACE PROCEDURE add_job_history
( p_emp_id ployee_id%type
, p_start_date job_history.start_date%type
, p_end_date d_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(:ployee_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 …]]}...]
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论