oracle触发器
一.实验目标
1.了解触发器的概念。
2.熟悉触发器的基本用法。
二.实验项目
准备:
create table emp1 as select * from emp;
create table emp2 as select * from emp;
create table emp3 as select * from emp;
create table emp4 as select * from emp;
create table dept1 as select * from dept;
将下面创建的触发器依次命名为obj5_1、obj5_2、…、obj5_6
1.创建一个emp1表的插入或修改触发器,功能是:员工的工资不能超过自己的经理的工资,超过则报错误。
create or replace trigger obj5_1
before insert or update on emp1
for each row
declare
v_sa emp1.sal%type;
PRAGMA AUTONOMOUS_TRANSACTION;
begin
select sal into v_sa from emp1 where deptno= :new.deptno and job='manager';
case
when INSERTING then
if :new.sal>v_sa
then
raise_application_error (-20001,'员工工资不能高于自己的经理工资');
end if;
when UPDATING then
if :new.sal>v_sa then
raise_application_error (-20001,'员工工资不能高于自己的经理工资');
end if;
end case;
end;
测试语句:
select * from emp1 where deptno=20;
select * from emp1 where deptno=20 and job='manager';
update emp1 set sal=30000 where empno=7369;
ROLLBACK;
update emp1 set sal=40000 where empno=7369;
ROLLBACK;
2.创建一个名为dept_summary(deptno,emp_count,sal_sum)的表,保存每一个部门的人数与工资总额。创建一个触发器,当对emp2表执行INSER