关闭

触发器-4

8991人阅读 评论(0) 收藏 举报

三、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.测试

 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1308789次
    • 积分:14910
    • 等级:
    • 排名:第797名
    • 原创:249篇
    • 转载:1篇
    • 译文:0篇
    • 评论:1条
    博客专栏