触发器-4

三、DML触发器
1.控制数据安全
案例01：
/*
限制用户只能在工作时间8：00到17：00修改表apple中的数据
*/

create or replace trigger tr_work_apple
before insert or update or delete on apple
begin
if to_char(SYSDATE, 'HH24') not between 9 and 17 then
raise_application_error(-20001,'不能在非工作时间修改该表数据');
end if;
end;

2.实现数据审计
/*
审计可以用于监视非法和可疑的数据库活动。
*/

案例参考前面的数据审计部分；

3.实现数据完整性
/*

希望员工的新工资不能低于原来的工资并且增幅不能超过20％

*/
create or replace trigger tr_check_emp_sal
before update of sal on emp_sal
for each row
when (new.sal<old.sal or new.sal>1.2*old.sal)
begin
raise_application_error(-20011,'工资只能增不降，并且增幅不能超过20％！');
end;

4.实现参照完整性
环境表：
create table p01
(sid int not null,
name varchar2(10)
);

create table p02
(
sid int,
score int
);

alter table p01
add constraint pk01 primary key(sid);

alter table p02
add constraint fk01 foreign key(sid) references p01 (sid);

insert into p01 values (1001, 'wind');
insert into p01 values (1002, 'snow');
insert into p01 values (1003, 'apple');
insert into p01 values (1004, 'mary');

insert into p02 values (1001, 99);
insert into p02 values (1002, 85);
insert into p02 values (1003, 96);
insert into p02 values (1004, 100);

问题：

当p01表更新数据时由于不能级联更新子表所以报错：

SQL> update p01 set sid=101 where name='wind';
update p01 set sid=101 where name='wind'
*
ERROR 位于第 1 行:
ORA-02292: 违反完整约束条件 (SCOTT.FK01) - 已找到子记录日志

解决：使用触发器实现级联更新
create or replace trigger tr_update_cascade_p01
after update of sid on p01
for each row
begin
update p02 set sid=:new.sid where sid=:old.sid;
dbms_output.put_line('子表也同时级联更新！');
end;

---------------------------------------------------------------
****************************************************************
四、替代触发器
****************************************************************
--------------------------------------------------------------
/*
对于简单的视图我们可以通过insert、update、delete操作，但是对于复杂的视图则不允许执行这些操作；
当满足下列任意一个条件时都不允许执行DML操作：
1.具体集合操作（union、union all、intersect、minus）
2.具有分组函数（min、max、sum、avg、count等）
3.具有group by、connect by、start with
4.具有distinct关键字
5.具有连接查询

为了使用DML语句修改表，需要使用替代触发器，但是建立instead of触发器有以下注意事项：
1.instead of只适用于视图
2.当基于视图建立触发器时不能指定before和after选项
3.在建立视图时没有指定with chekc option
4.当建立触发器时必须指定for each row

*/

--案例01：替代触发器
1)替代触发器子能应用于行级
A.新建视图
create or replace view v01
as
select stu.stuid, stu.stuname, sub.subjectid, sub.subjectname,
sc.score from student stu, subject sub, score sc
where stu.stuid=sc.stuid and sc.subjectid=sub.subjectid;

B、新建替代触发器
create or replace trigger tr02
instead of insert on v01
referencing new n
for each row
declare
icount int;
begin
select count(*) into icount from student where stuid=:n.stuid;
if icount=0 then
insert into student (stuid, stuname) values (:n.stuid, :n.stuname);
else
update student set student.stuname=:n.stuname where stuid=:n.stuid;
end if;
select count(*) into icount from subject where subjectid=:n.subjectid;
if icount=0 then
insert into subject values (:n.subjectid, :n.subjectname);
else
update subject set subject.subjectname=:n.subjectname where subjectid=:n.subjectid;
end if;
select count(*) into icount from score where subjectid=:n.subjectid and stuid=:n.stuid;
if icount=0 then
insert into score values (:n.stuid, :n.subjectid, :n.score);
else
update score set score.score=:n.score where subjectid=:n.subjectid and stuid=:n.stuid;
end if;
end;

C.测试