大型数据库系统实验三

实验三触发器
一、实验目的
掌握触发器的设计方法。

二、实验内容
1、创建一个触发器delete_dept_tri，在删除dept表数据前触发，删除emp表中对应deptno 的数据。

create or replace trigger delete_dept_tri
before delete on dept
for each row
begin
delete from emp where deptno= :new.deptno;
end;
2、创建一个触发器sal_tri，确保修改后员工的工资不能低于原有工资的1.2倍，如果修改后员工的工资不能低于原有工资的1.2倍，就立即触发自动将员工的工资修改为原有工资的1.2倍。

create or replace trigger sal_tri
before update on emp
for each row
when (new.sal<(old.sal*1.2))
begin
:new.sal:=(:old.sal*1.2);
end;
3、创建INSTEAD OF触发器
（1）创建一个视图：
Create view emp_dept as
Select empno,ename,emp.deptno,dname
From emp,dept
Where emp.deptno=dept.deptno
（2）创建触发器，当向视图emp_dept插入数据时，替代为对emp表和dept表插入相应数据。

create or replace trigger emp_dept
instead of
insert on emp_dept
for each row
begin
insert into dept(DEPTNO,DNAME)
values(:new.deptno,:new.dname);
insert into emp(empno,ename,deptno)
values(:new.empno,:new.ename,:new.deptno);
end;
（3）向视图emp_dept插入数据。

SQL> insert into emp_dept(empno,ename,deptno,dname)
2 values('2323','abac','10','df');
1 row inserted
结果查询：
SQL> select * from emp_dept;
EMPNO ENAME DEPTNO DNAME
----- ---------- ------ --------------
7499 ALLEN 30 SALES
7521 WARD 30 SALES
2323 abac 10 df
7698 BLAKE 30 SALES
7934 MILLER 40 OPERATIONS
4、创建一个触发器total_emp_tri，当emp表中发生了任何变化，立即触发，然后对emp 表的数据进行统计，统计员工的总数和平均工资，统计结果存储在emp_status中。

（1）创建表emp_status，包含两个字段：total_emps和avg_sal。

SQL> create table emp_status(total_emps number(4),avg_sav
number(7,2));
Table created
（2）创建触发器update_emp_tri，在表emp发生插入、更新和删除之后，对表emp数据进行统计员工的总数和平均工资，存储在表emp_status中。

create or replace trigger update_emp_tri
after insert or update or delete on emp
declare
new_avg_sav emp_status%rowtype;
begin
select count(empno),avg(sal) into new_avg_sav from emp;
insert into emp_status(total_emps,avg_sav)
values(new_avg_sav.total_emps,new_avg_sav.avg_sav);
end;
三、实验小结
本次实验内容主要是触发器的设计，主要是dml触发器和代替触发器。

其中before和after：指在事件发生之前或之后激活触发器。

instead of：如果使用此子句，表示可以执行触发器代码来代替导致触发器调用的事件。

insert、delete和update：指定构成触发器事件的数据操纵类型，update还可以制定列的列表。

for each row：指定是否对受影响的每行都执行触发器，即行级触发器，如果不使用此子句，则为语句级触发器。

when：限制执行触发器的条件，该条件可以包括新旧数据值得检查。