Oracle触发器详解
- 格式：pdf
- 大小：232.42 KB
- 文档页数：6
Oracle触发器详解
开发中肯定会⽤到Oracle的触发器，本⽂进⾏详细讲解。
这⾥实例中⽤到的主要是Oracle中scott⽤户下的emp以及dept表，数据如下
⼀、触发器概念
1、概念：
触发器的本质是⼀个存储过程，顾名思义发⽣特定事件时Oracle会执⾏触发器中的代码。
细分它的组成可以分为3个部分：第⼀部分在什么条件下触发器会执⾏，即触发器被触发的事件。第⼆部分
在什么时间点执⾏触发器
即触发器的发⽣事件例如before,after。第三部分触发器⾃⾝所要做的事情，就是触发器被触发以后具体想
表达的事件，在begin和end
之间的sql。
⼆、触发器的分类：
1、ddl触发器：即执⾏ddl操作后所触发的事件。
常⽤的ddl操作有：grant（授权），revoke（撤销授权），create（创建），drop（删除），alter（修
改），comment（注释），audit（审核），rename（重命名）
在进⾏具体实例以前先来讲解另⼀个概念：oracle中的user和schema：
user：oracle中的⽤户，拥有数据库的对象以及对数据库对象增删改查的权限。schema：该⽤户下所有数
据库对象的集合Collection.类似于⽣活中
房⼦schema和房⼦的拥有者user之间的关系，你是⼀个⽤户user你可以通过alter session查看别⼈的房
⼦，但是你是否可以改变房⼦中的家具，要看这个房⼦的拥有者是否grant你这个权限，除⾮你是所有房⼦
的最⾼权限⼈dba。
ddl Example：禁⽌scott⽤户的所有ddl操作
CREATE OR REPLACE TRIGGER scott_trigger
BEFORE DDL
ON SCHEMA
BEGIN
RAISE_APPLICATION_ERROR(-20008,'禁⽌scott⽤户的所有ddl操作');
END;
create sequence myseq;
这⾥看到在创建触发器以后如果仍然使⽤ddl操作，便会报错。
2、dml触发器：基于dml操作的触发器，细分⼜可以分为⾏触发器和语句触发器。
A、语句触发器：dml操作可能会影响很多⾏，主要⽤于对数据的安全保护。
Example：禁⽌在周四，周五修改emp表数据
CREATE OR REPLACE TRIGGER emp_trigger
BEFORE UPDATE OR DELETE OR INSERT
ON emp
BEGIN
IF to_char(sysdate,'day') IN ('星期四','星期五') THEN
RAISE_APPLICATION_ERROR(-20008,'不允许在周四周五修改emp表');
END IF;
END;
update emp set sal=800;
这⾥建⽴触发器以后，当你想改变所有⼈的⼯资时就会出触发器的错误，所有⼈的⼯资即表⽰会影响很多
⾏。
B、⾏级触发器：针对需要操作的那⼀⾏，有关键词：for each row,⽤来
（1）实现数据的审计功能：
Example:做⼀个记录删除员⼯信息的表记录被删除员⼯的信息
这⾥为了不改变oracle中emp表的数据，新建⼀个emp_new表
create table emp_new
as
select * from emp;
create table emp_audit(name varchar2(10),delete_time Date);
CREATE OR REPLACE TRIGGER delete_trigger
AFTER DELETE ON emp_new
FOR EACH ROW
BEGIN
INSERT INTO emp_audit values(:old.ename,sysdate);
END;
delete from emp_new where empno='7499';
select * from emp_audit;
这⾥可以看到在创建触发器时，⽤到了for each row关键词，：old.***⽤来表⽰更改以前的表中的数据，:new.***⽤来表⽰更改以后的数据，在删除数据以后在⽇志表就有对应的记录。
（2）实现数据完整性：
Example:要求员⼯涨⼯资后，不能低于原来的⼯资，所涨⼯资也不能⾼于原来的50%。
这⾥为了不改变oracle中emp表的数据，新建⼀个emp_new表
create table emp_new
as
select * from emp;
CREATE OR REPLACE TRIGGER emp_trigger
BEFORE UPDATE OF sal ON emp_new
FOR EACH ROW
WHEN (new.sal1.5*old.sal)
BEGIN
RAISE_APPLICATION_ERROR(-20008,'⼯资只增不降，且涨幅不可⼤于50%');
END;
update emp_new set sal = 1.6*sal where empno='7788';
这⾥可以看到当改变数据时会触发触发器错误，对表中某⼀个字段的修改⽤UPDATE OF即可，另外如果
new和old在PLSQL块的外部
即BEGIN外⾯不可以加冒号。
（3）参照完整性：
Example：主要⽤于级联更新，如更新dept表中的deptno时，emp表的deptno也更新。
这⾥仍然新建2个表分别和emp表dept表的数据相同。
create table emp_new
as
select * from emp;
create table dept_new
as
select * from dept;
CREATE OR REPLACE TRIGGER cascade_trigger
AFTER UPDATE OF deptno ON dept_new
FOR EACH ROW
BEGIN
UPDATE emp_new SET deptno=:new.deptno WHERE deptno=:old.deptno;
END;
update dept_new set deptno=15 where deptno=20;
select * from dept_new;
select * from emp_new;
这⾥参照完整新指具有主从关系的多个表，当更新主表主键时需要更新从表的相关数据。
3、替代触发器：
这⾥先讲另⼀个概念：带有with check option的视图：
如果视图的定义包括条件（如where⼦句）并且任何应⽤于该视图的INSERT或UPDATE语句都应包括该条
件，则必须使⽤WITH CHECK OPTION定义该视图。
Example：
CREATE VIEW emp_view
(ename,empno)
AS SELECT ename,empno FROM emp
WHERE deptno=20
WITH CHECK OPTION;
这⾥有个条件部门号为20，则任何修改这个视图的语句都必须针对的是20号部门的员⼯。
继续替代触发器的概念：关键字insteadof，主要针对⼀些复杂的视图，因为级联表所产⽣的视图不可以使
⽤update,insert,delete等关键字，没有before，after等关键字，并且不可以建⽴在with check option选项的
视图上，⽐如新建⼀个emp表和dept表的级联视图，则不可以向其中添加数据，现在通过触发器解决：
Example:
仍然新建2个表分别和emp表dept表的数据相同。
CREATE TABLE emp_new
AS
SELECT * FROM emp;
CREATE TABLE dept_new
AS
SELECT * FROM dept;
CREATE VIEW emp_dept
AS
SELECT d.deptno,d.dname,e.empno,e.ename
FROM dept_new d,emp_new e
WHERE d.deptno=e.deptno;
这⾥scott⽤户需要先通过sysdba授权才能建⽴视图：
grant create view to scott;
CREATE OR REPLACE TRIGGER insteadof_trigger
INSTEAD OF INSERT ON emp_dept
FOR EACH ROW
DECLARE
v_temp INT;
BEGIN
SELECT COUNT(*) INTO v_temp FROM dept_new WHERE deptno=:new.deptno;
IF v_temp=0 THEN
INSERT INTO dept_new(deptno,dname) VALUES(:new.deptno,:new.dname);
END IF;
SELECT COUNT(*) INTO v_temp FROM emp_new WHERE empno=:new.empno;
IF v_temp=0 THEN
INSERT INTO emp_new(deptno,empno,ename) VALUES(:new.deptno,:new.empno,:new.ename);
END IF;
END;
INSERT INTO emp_dept values(15,'HUMANRESOURCE',7999,'LEAF');
