第16讲 开发L-SQL子程序.pptVIP

任务4：使用触发器 （3）语句触发器测试 SQL insert into emp (empno,ename) values (1000,张三); SQL update emp set job=文员,sal=1500 where empno=1000; SQL delete from emp where empno=1000; 任务4：使用触发器 练习：在dept表上创建一个语句触发器，监视用户对dept表的操作，将用户对dept的所有DML操作记录到日志表中。 任务4-1：语句级触发器 任务4：使用触发器 如果在某个表上创建了一个触发器，在对这个表进行DML操作时，每影响一行数据，该触发器都将被激发执行一次，那么这个触发器就是行级触发器。 任务4-2：行级触发器 为了能够比较修改前和修改后的数据，在触发器的可执行代码中，可以使用两个关联行标识符NEW和OLD。它们分别表示触发器被激发时，当前行的原数据和新数据。 对于UPDATE命令，OLD表示原来的行，NEW表示修改后的行。对于INSERT命令，OLD没有意义，NEW表示新写入的一行。对于DELETE命令，NEW没有意义，OLD表示被删除的一行。 任务4：使用触发器 任务4-2：行级触发器 例：使用行级触发器监视emp表上的dml操作，监视内容如下： 1）在DML操作执行之前进行合法性检查。 2）如果要从表emp中删除一行数据，不能删除部门30的员工。如果是其他部门的员工，则删除这行数据，并把这一行数据在另一个表中进行备份。 3）如果要写入一行数据，要保证这个员工的工资高于1000。 4）如果修改表emp的sal列，应保证新工资比原工资要高，并把员工的工资变化情况记录在另一个表中。 任务4：使用触发器 （1）创建delete操作的审计表audit_del_emp create table audit_del_emp as select * from emp where deptno=0; 添加操作时间（Oper_time）和操作者（Oper_user）： alter table audit_del_emp add( oper_time varchar2(20), oper_user varchar2(20) ); 任务4：使用触发器 （2）创建update操作的审计表audit_update_emp 任务4：使用触发器 （3）创建行级触发器 create or replace trigger emp_dml_rowtrg before insert or update or delete of sal on emp for each row begin --添加新雇员检查 --更新雇员工资检查 --解雇雇员检查 end; / 任务4：使用触发器 --添加新雇员检查 if inserting then if (:new.sal is null)or(:new.sal1000) then raise_application_error(-20000,新雇员的工资不得低于1000); end if; end if; 任务4：使用触发器 --更新雇员工资检查 if updating then if :new.sal=:old.sal then raise_application_error(-20001,雇员||:old.empno||工资没有增加); else insert into audit_update_emp (empno,ename,old_sal,new_sal,oper_time,oper_user) values (:old.empno,:old.ename,:old.sal,:new.sal,to_char(sysdate,yyyy-mm-dd hh24:mi:ss),user); end if; end if; 任务4：使用触发器 --解雇雇员检查 if deleting then if :old.deptno=30 then raise_application_error(-20002,部门||:old.deptno||的雇员不得删除); else insert into audit_del_emp (empno,ename,job,mgr,hiredate,sal,comm,deptno,oper_time,oper_user) values (:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sa