触发器-3

------------------------------------------------------
第二部分：after触发器
------------------------------------------------------

--案例01：新建一个测试的行后触发器：行级触发器

/*行级触发器即每操作一行都会触发一次触发器*/
create or replace trigger tr02
after update on student
for each row /* each row说明是个行级别触发器*/
begin
dbms_output.put_line('这个是行级update触发器！');
/*操作一条语句就触发一次这个触发器！*/
end;

--案例02：新建一个after触发器

--下面是库存表
create table goodstore
(
goodsno int primary key, --库存商品编号
goodscount int --库存商品数量
);

--下面是商品出库表
create table goodstoreout
(
goodsno int,
goodcount int,
goodoutdate date default sysdate
);

insert into goodstore values (1001, 100);
insert into goodstore values (1005, 200);
insert into goodstore values (1002, 300);
insert into goodstore values (1003, 500);
insert into goodstore values (1004, 600);

--编号为1001的商品出库20件
insert into goodstoreout values (1001, 20, default);

--查询出库表
select * from goodstoreout;

--查询库存表
select * from goodstore; --发现1001数据量有减少

--定义触发器
create or replace trigger tr04
after insert on goodstoreout
for each row
begin
update goodstore set goodscount=goodscount-:new.goodcount
where goodsno=:new.goodsno;
dbms_output.put_line('本次出库的商品编号：' ||:new.goodsno || ' ' || '出库的数量为：' ||to_char(:new.goodcount));
end;

--案例03：审计工资变化
1.需要的环境表
create table emp_sal
(
eid int,
name varchar2(10),
sal number(6,2)
);

insert into emp_sal values (1001, 'wind', 1800);
insert into emp_sal values (1002, 'snow', 2800);
insert into emp_sal values (1003, 'mary', 1500);

2.记录工资变化的审计表
create table audit_emp_sal_change
(
name varchar2(10),
oldsal number(6,2),
newsal number(6,2),
time date
);

3.新建用于审计的触发器

create or replace trigger tr_audit_emp_sal_change
after update of sal on emp_sal
for each row
declare
cnt int;
begin
select count(*) into cnt from audit_emp_sal_change where name=:old.name;
if cnt=0 then
insert into audit_emp_sal_change values (:old.name,:old.sal,:new.sal,sysdate);
else
update audit_emp_sal_change set oldsal=:old.sal, newsal=:new.sal, time=sysdate where name=:old.name;
end if;
end;

--案例04：DML触发器使用注意事项
1.新建触发器

create or replace trigger tr_audit_emp_sal_change
after update of sal on emp_sal
for each row
declare
maxsal number(6,2);
begin
select max(sal) into maxsal from emp_sal;
if :new.sal >maxsal then
raise_application_error(-20011,'超出工资上限！');
end if;
end;

2.错误信息

SQL> select * from emp_sal;

EID NAME SAL
---------- ---------- ----------
1001 wind 2600
1002 snow 2800
1003 mary 1500
1004 kil 2500

SQL> update emp_sal set sal=6000 where eid=1004;
update emp_sal set sal=6000 where eid=1004
*
ERROR 位于第 1 行:
ORA-04091: 表 SCOTT.EMP_SAL 发生了变化，触发器/函数不能读
ORA-06512: 在"SCOTT.TR_AUDIT_EMP_SAL_CHANGE", line 4
ORA-04088: 触发器 'SCOTT.TR_AUDIT_EMP_SAL_CHANGE' 执行过程中出错