【Oracle篇】触发器的使用 [下]

转载请注明出处：http://blog.csdn.net/feng1790291543

-- 触发时机： before | after
-- before、after触发器的执行时机：分别在DML操作的“执行缓存”之前或者之后执行；
-- 无论before、after触发器，都会在真正DML语句执行前执行。
/**
    before 、 after 触发器的区别：
    
    1- 触发时机不同。
    
    2- 行级前触发器可以修改 :new 的值，而行级后触发器不能。否则编译错误
    
*/
-- 注意：视图触发器都是 行级触发器。


-- 变异表：是触发表的一种状态（脏数据状态）；
-- 脏数据：正在变化的数据；

--- 注意： 不能读取变异表（ 在行级触发器中，不能对触发表进行查询）。
-- 解决方案：----->变异表
-- 1- 定义一个包，在包中定义一个可以存放部门编号的public变量
-- 2- 定义一个行级 前|后 触发器，将操作的:new.deptno放入包变量。
-- 3- 定义一个表级后触发器读取包变量中部门编号，实现逻辑。

-- 1：

-- 建立视图：
create view v_e_d as select e.*,d.dname,d.loc from emp e ,dept d where e.deptno=d.deptno;
-- 建立这个视图的 instead of 触发器，监听 update事件。
-- 如果修改的不是deptno，直接修改： 需要区分 emp表的数据还是 dept表数据。
-- 如果修改的是deptno,需要判断这个新修改的编号，在 dept表中是否有，则修改emp当前员工部门编号为新编号
--                    如果修改的编号不存在，则在dept表中新建这个部门，将新编号赋予当前员工编号。

create or replace trigger tri_em_d_b
  instead of update on v_e_d  
  for each row
declare
  v_num number;
  v_dno number;
  cursor cur_emp is select e.* from emp e for update;
  --cursor cur_dep is select d.* from dept d for update; 
   type my_record is record(
        r_empno   emp.empno%type,
        r_ename   emp.ename%type,
   --     r_deptno  emp.deptno%type,
        r_dname   dept.dname%type,
        r_sal     emp.sal%type,
        r_comm    emp.comm%type,
        r_job     emp.job%type,
        r_mgr     emp.mgr%type,
        r_hir     emp.hiredate%type,
        r_loc     dept.loc%type
   );
   -- 使用记录类型，定义一个具体的记录
   v_record my_record;
   
begin
  select seq_ed.nextval into v_num  from dual;
  select deptno into v_dno from emp where empno=:new.EMPNO;
  select e.empno,e.ename,d.dname,e.sal,e.comm,e.job,e.mgr,e.hiredate,d.loc 
  into v_record
  from emp e ,dept d where e.deptno=:new.DEPTNO;
  --update emp set v_dno=:new.DEPTNO;
  
  if updating then
   if  :old.DEPTNO=v_dno
     then
       dbms_output.put_line('可以直接修改,over!');
        
    else
     for v_emp in cur_emp loop
        if v_emp.deptno=v_dno
          then 
             v_emp.deptno:=v_dno;
         else
           --then 
             v_emp.deptno:=v_num;         
          end if;
       end loop;  
       dbms_output.put_line('只能修改deptno,over!');
     end if;
   end if; 
end tri_em_d_b;

-- 2：
-- 处理变异表问题：
-- 定义触发器完成：新加入部门的员工（insert、update），其工资不能高于本部门所有员工的平均工资，否则报异常。

-- 建立一个包
create or replace package pak_dep
is
    v_dno number;
end;

-- 建立一个行级触发器
create or replace trigger tri_em_new_b
  before  update or insert on emp2  
  for each row
declare
begin
      pak_dep.v_dno:=:new.deptno;
end tri_em_new_b;

-- 建立一个表级触发器
create or replace trigger tri_em_new_s
  before  update or insert on emp2  
declare
   v_sal number;
   v_avgsal number;
begin
    select sal into v_sal from emp where deptno=pak_dep.v_dno;
    select avg(sal) into v_avgsal from emp where deptno=pak_dep.v_dno; 
    
    if sal > v_avgsal then
      raise_appliaction_error(-20040,'工资不得高于本部门平均工资');
     end if;
end tri_em_new_s;


-- 3：
--使用行级前触发器，修改 :new.empno,给insert的员工赋予编号，
--规则：  员工编号 = 年份后两位+月份+2为序列号

create table emp6 as select * from emp;

create or replace trigger tri_em_in_b
  before  update or insert on emp6  
  for each row
declare
   v_num number;
   v_sq number;
begin
    select seq_in.nextval into v_sq from dual;  
    select concat(concat(substr(to_char(sysdate,'yyyy'),3,2),substr(to_char(sysdate,'mm'),1,2)),v_sq)num 
    into v_num from dual;
    :new.empno:=v_num;
end tri_em_in_b;