一、什么是触发器
触发器类似于函数和过程，它们都是具有声明、执行部分和异常处理部分的PL/SQL块。
触发器必须在数据库中以独立对象的身份存储，不能定义到包中。
过程是显式地通过调用而执行的，并且可以传参；与之相反，触发器是在事件发生时隐式地运行的，并且触发器不能接收参数。
运行触发器的方式叫做激发（firing）触发器。其触发的事件可以是：
对数据库表的DML操作
对视图的操作
可以激发系统事件（如：启动、关闭数据库）……
二、触发器的作用
1 、可以用来自动审计数据内容。
2 、在内容发生变更时，自动通知其他程序采取相应的处理。
三、触发器的类型
• DML 触发器
• 替代触发器
• 系统触发器
四、创建触发器的语法
- CREATE [OR REPLACE] TRIGGER trigger_name
- {BEFORE | AFTER | INSTEAD OF}
- [FOR EACH ROW]
- trigger_body;
其中，trigger_name是触发器的名称，triggering_event说明了激发触发器的事件（也可能包括特殊的表或视图），trigger_body是触发器的代码。referencing_clause用来引用正在处于修改状态下的行中的数据，如果在WHEN子句中指定trigger_condition的话，则首先对该条件求值。触发器主体只有在该条件为真值时才运行。
注意触发器主体不能超过32K。
五、DML触发器
（一）分类
1 、根据触发事件划分
insert/update/delete
2 、根据触发时间划分
before—— 先触发后执行
after—— 先执行后触发
3 、根据触发级别划分
语句级：每个dml语句不论影响了多少行都只触发1次
记录级：针对dml语句影响的每个行都会触发一次
Dml 触发器案例一
要求：用户下班时间和周末不能更改emp表
分析思路：
触发事件：对emp表的update、insert、delete操作
触发时间：使用前触发可以避免不必要的回滚所以选择before触发
触发级别：每个语句触发一次就可以
代码描述：当用户对表进行更改操作的时候，判断当前的系统时间，如果是周末或者下班时间就回滚或抛出异常
- create or replace trigger myti2
- before insert or update or delete on emp
- declare
- i varchar2( 20 );
- j varchar2( 20 );
- begin
- select to_char(sysdate, 'day' ) into i from dual;
- select to_char(sysdate, 'hh24' ) into j from dual;
- if i= ' 星期六' then
- raise_application_error(- 20082 , ' 不能在非工作时间更改表' ); -- 注意，错误号在20000--20999
- elsif i= ' 星期日' then
- raise_application_error(- 20082 , ' 不能在非工作时间更改表' );
- elsif j< '08' then
- raise_application_error(- 20082 , ' 不能在非工作时间更改表' );
- elsif j> '17' then
- raise_application_error(- 20082 , ' 不能在非工作时间更改表' );
- else
- null;
- end if;
- end myti2;
- insert into emp(empno) values( 9999 );
说明：Oracle的触发器中不支持回滚操作。
Dml 触发器案例二
要求：用户对emp表的sal更改量不能超过10%
分析思路：
触发事件：对emp表的update操作
触发时间：使用前触发可以避免不必要的回滚所以选择before触发
触发级别：因为会影响到多行因此使用行触发，针对被更改的每一行都触发一次
代码描述：当用户对表进行更改操作的时候，根据更改后的sal(使用:new.sal)和更改前的sal(使用:old.sal)，判断更改量是否超过10%，如果超出了就抛出异常。
注意：如果要使用:old和:new一定是行触发(for each row)
- create or replace trigger myti1
- before update on emp for each row
- begin
- if abs(:new.sal-:old.sal)/:old.sal > 0.1 then
- raise_application_error(- 20001 , ' 改动过大' );
- end if;
- end;
- update emp set sal= 1500 where empno= 7369 ;
Dml 触发器案例三
要求：现在有两个表，商品库存表，商品交易表，如果交易表中插入记录a商品进货10个，那么a商品的库存就增加10个，如果销售10个，a的库存就减少10个，如果请求的数量超出库存，我们就提示出错
分析思路：
触发事件：对商品交易表的insert操作
触发时间：使用前触发可以避免不必要的回滚所以选择before触发
触发级别：因为要使用:old和:new一定是行触发(for each row)
代码描述：根据插入的记录首先判断交易类型，如果是进货就更改商品库存表把相应商品的库存加上进货数量，如果是出货首先判断该商品是否有足够的库存，如果有就更改该库存，如果库存不足就抛出异常。
- create table kucun(pid number,num number);
- create table churuku(pid number,inorout varchar2( 5 ),n number);
- insert into kucun values( 123 , 20 );
- insert into kucun values( 321 , 20 );
- create or replace trigger mytri3
- before insert on churuku for each row
- declare
- i number;
- begin
- if :new.inorout= 'out' then
- select num into i from kucun where pid=:new.pid;
- if i >= :new.n then
- update kucun set num=num-:new.n where pid=:new.pid;
- else
- raise_application_error(- 20009 , ' 库存不足' );
- end if;
- else
- update kucun set num=num+:new.n where pid=:new.pid;
- end if;
- end;
- insert into churuku values( 123 , 'in' , 10 );
- select * from kucun
- select * from churuku
六、详解DML触发器
DML 触发器是由对数据库表进行insert、update、delete操作而激发的触发器
DML 触发器可以设定语句、定时和触发级别
DML 触发器类型
类别 | 值 | 说明 |
语句 | insert 、update、delete | 定义何种DML语句激发触发器 |
定时 | before 、after | 定义触发器是在语句运行前或运行后激发 |
级别 | 语句级、行级 | 如果是行级触发器，该触发器就对由触发语句变更的每一行激发一次。 如果是语句级触发器，则该触发器就在语句之前或之后激发一次。 行级触发器定义语句for each row |
DML 触发器激发顺序：
1、 语句级、之前级触发器
2、 行级触发器
（1）之前级触发器
（2）执行该语句本身（insert、update、delete）
（3）之后级触发器
3 、语句级、之后级触发器
行级触发器的相关标识
行级触发器是按触发语句所处理的行激发的。在触发器内，我们可以访问正在处理中的行的数据。行级访问的两个PL/SQL宿主变量:old、:new
触发语句 | :old | :new |
Insert | 无定义，所有列为null | 将插入的值 |
Update | 更新前，行的原始值 | 将更新的值 |
delete | 行删除前的原始值 | 无定义，所有列为null |
—— | 具有只读属性，只能读入 | 可以修改值 |
例：为students表制做一个标识列
- create table students(id number( 5 ),first_name varchar2( 10 ),last_name varchar2( 10 ));
- create sequence stu_num
- create or replace trigger genstuid
- before insert on students for each row
- begin
- select stu_num.nextval into :new.id from dual;
- end;
- insert into students(first_name,last_name) values( 'Anni' , 'Scott' );
- select * from students
说明：:old和:new记录不能传递到过程和函数中。
为:old和:new指定一个不同的名称
语法：
- Referencing [old as :old_name] [new as :new_name]
例：修改上例的触发器
- create or replace trigger genstuid
- before insert or update on students
- referencing new as new_stu
- for each row
- begin
- select stu_num.nextval into :new_stu.id from dual;
- end;
触发器谓语：inserting、updating、deleting
表达式 | 谓语状态 |
Inserting | 如果触发语句是insert的话，则为真(true)，否则为假(false) |
Updating | 如果触发语句是update的话，则为真(true)，否则为假(false) |
deleting | 如果触发语句是delete的话，则为真(true)，否则为假(false) |
例：
-- 学员注册信息表
- create table rs_stu(stu_id number( 5 ),dep char( 10 ),course number( 10 ),grade char( 10 ))
- -- 学员信息审计表(日志)
- create table stu_audit(change_type char( 1 ) not null,
- changed_by varchar2( 8 ) not null,
- timestamp date not null,
- old_stu_id number( 5 ),
- old_dep char( 10 ),
- old_course number( 10 ),
- old_grade char( 10 ),
- new_stu_id number( 5 ),
- new_dep char( 10 ),
- new_course number( 10 ),
- new_grade char( 10 ));
- -- 针对增、删、改的审计触发器
- create or replace trigger logstuchanges
- before insert or delete or update on rs_stu for each row
- declare
- v_changetype char( 1 );
- begin
- if inserting then
- v_changetype:= 'I' ;
- elsif updating then
- v_changetype:= 'U' ;
- else
- v_changetype:= 'D' ;
- end if;
- insert into stu_audit(change_type,changed_by,timestamp,
- old_stu_id,old_dep,old_course,old_grade,
- new_stu_id,new_dep,new_course,new_grade)
- values(v_changetype,user,sysdate,
- :old.stu_id,:old.dep,:old.course,:old.grade,
- :new.stu_id,:new.dep,:new.course,:new.grade);
- end;
- -- 测试
- insert into rs_stu values( 200 , 'B' , 3 , 2 );
- update rs_stu set dep= 'C' where stu_id= 200
- delete from rs_stu
- select * from rs_stu
- select * from stu_audit
七、替代触发器
1 、定义
DML 触发器是除去执行insert、update或delete操作外，还要被激活运行的触发器，而替代触发器则被激发来代替执行DML语句。
替代触发器还可以定义在视图上，而DML触发器只能定义在表上。
替代触发器是行级的。
2 、用途
（1）允许对无法变更的视图进行修改；
（2）修改视图中嵌套表的列。
3 、可变更的与不可变更的视图
一般来说，视图如果不包括下列命令的就是一个可变更的视图：
l 集合操作（union，union all，minus，intersect）
l 聚合函数（sum，avg，count）
l Group by
l Distinct
l 联合查询
例：
-- 分组计算每个部门的总销售额的视图
create view myview as
select deptno,sum(sal) tot from emp group by deptno;
-- 让10部门销售额翻一番
update myview set tot=tot* 2 where deptno= 10 ;
说明：这个对视图的更新不会成功，因为该视图用了聚合函数、分组
-- 我们做个替代触发器，来代替上面的更新语句完成任务
create or replace trigger mytri instead of update on myviewfor each row
declare
i number;
begin
select count(*) into i from empwhere deptno=:new.deptno;
update emp set sal=sal+(:new.tot-:old.tot)/iwhere deptno=:new.deptno;
end;
4 、一个复杂的不可更该视图的替代触发器
例：
-- 分组计算每个部门的工资总额的视图
create view v1(deptno,sal) as
select deptno,sum(sal) from emp group by deptno;
-- 计算工资的触发器。例如，我们将10部门的工资从6000改为20000，我们就把增长的部分按比例分配给该部门的每个员工。反之如此
create or replace trigger tu instead of update on v1 for each row
declare
w number;
s emp.sal%type;
cursor cur is select * from empwhere deptno=:new.deptno;
q number;
r number;
temp number;
begin
s:=:new.sal-:old.sal; -- 修改的差值
temp:=s; -- 修改后的值
select count(*) into q from empwhere deptno=:new.deptno;
if s>= 0 then
for i in cur
loop
if cur%rowcount<q then
w:=i.sal/:old.sal; -- 先求每个职工的工资百分率
temp:=temp-abs(w*s); --w*s 是按比例分配更新值
update emp set sal=sal+w*s where empno=i.empno;
elsif cur%rowcount = q then
update emp set sal=sal+temp where empno=i.empno;
end if;
end loop;
else
for i in cur
loop
if cur%rowcount < q then
w:=i.sal/:old.sal;
temp:=temp+abs(w*s);
update emp set sal=sal+w*s where empno=i.empno;
elsif cur%rowcount = q then
update emp set sal=sal+temp where empno=i.empno;
end if;
end loop;
end if;
end;
-- 修改上例，四舍五入到整数
create or replace trigger tu instead of update on v1for each row
declare
w number;
s emp.sal%type;
cursor cur is select * from empwhere deptno=:new.deptno;
q number;
r number;
temp number;
begin
s:=:new.sal-:old.sal; -- 修改的差值
temp:=s; -- 修改后的值
select count(*) into q from empwhere deptno=:new.deptno;
if s>= 0 then
for i in cur
loop
if cur%rowcount<q then
w:=i.sal/:old.sal; -- 先求每个职工的工资百分率
select round(w*s,0) into r from dual;
temp:=temp-abs(r);
update emp set sal=sal+r where empno=i.empno;
elsif cur%rowcount = q then
update emp set sal=sal+temp where empno=i.empno;
end if;
end loop;
else
for i in cur
loop
if cur%rowcount < q then
w:=i.sal/:old.sal;
select round(w*s,0) into r from dual;
temp:=temp+abs(r);
update emp set sal=sal+r where empno=i.empno;
elsif cur%rowcount = q then
update emp set sal=sal+temp where empno=i.empno;
end if;
end loop;
end if;
end;
-- 删除语句触发器
create or replace trigger tid instead of insert or delete on v1
begin
dbms_output.put_line( ' 本视图只能进行更新，没有插入和删除功能！' );
end;
-- 测试
select * from emp where deptno= 10 ;
update v1 set sal= 17000 where deptno= 10 ;
select * from emp where deptno= 10 ;
rollback;
delete from v1 where deptno= 10 ;
八、删除、禁止触发器
1 、删除触发器
语法：
- Drop trigger 触发器名;
2 、禁止与允许触发器
与过程和包不同，触发器可以被禁止使用
语法：
- Alter trigger 触发器名 disable|enable;
3 、禁止与允许整个表的触发器
语法：
- Alter table 表名 disable|enable all triggers;
扫一扫
484
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
