源码-Oracle数据库管理-第十五章-触发器和游标-Part 2（理解触发器）

利用触发器，可以让Oracle自动完成一些任务，如，日志记录，比较有意思。

心得：在PL/SQL Developer工具中，在script中运行 create or replace trigger trigger_name代码段，trigger并不会自动编译。需要在对象浏览器中编辑并调试。刚开始不知道，还以为是工具的bug呢。

--2016/12/31
--代码 15.6 使用REFERENCING子句指定别名
CREATE OR REPLACE TRIGGER t_vsal_ref
   BEFORE UPDATE ON emp               --触发器作用的表对象以及触发的条件和触发的动作
   REFERENCING OLD AS emp_old NEW AS emp_new
   FOR EACH ROW                         --行级别的触发器
   WHEN(emp_new.sal>emp_old.sal)         --触发器条件
DECLARE
   v_sal   NUMBER;                        --语句块的声明区
BEGIN
   IF UPDATING ('sal') THEN                 --使用条件谓词判断是否是sal列被更新
      v_sal := :emp_new.sal - :emp_old.sal;    --记录工资的差异
     DELETE FROM emp_history 
            WHERE empno = :emp_old.empno; --删除emp_history中旧表记录
     INSERT INTO emp_history              --向表中插入新的记录
                 VALUES (:emp_old.empno, :emp_old.ename, :emp_old.job, :emp_old.mgr, :emp_old.hiredate,
                        :emp_old.sal, :emp_old.comm, :emp_old.deptno);
     UPDATE emp_history                   --更新薪资值
         SET sal = v_sal
       WHERE empno = :emp_new.empno;
   END IF;
END;

select * from emp_history;
update emp set comm=2017 where empno=5093 or empno=4093;
commit;
select * from emp;
drop trigger t_emp_log;
insert into emp values(5093,'pd','conlt',9098,null,20000000,2700,10);
drop trigger t_vsal_ref;
delete * from emp_history;
truncate table emp_history;
drop trigger t_verifysalary;
drop trigger t_emp_comm;


alter table emp_history add update_date DATE;
alter table emp_history rename column sal to sal_before_update;
alter table emp_history drop column update_date;
alter table emp_history add sal_after_update NUMBER(20,2);
--alter table emp_history modify update_date DATETIME;
drop trigger trigger_emp_update;
drop trigger trigger_emp_update_log;
drop trigger t_emp_comm;
insert into emp_history values(1111,'test','ba','2222',null,1500,300,20,20000)

--代码15.7 使用WHEN子句指定触发器执行条件
CREATE OR REPLACE TRIGGER t_emp_comm
   BEFORE UPDATE ON emp                   --触发器作用的表对象以及触发的条件和触发的动作
   FOR EACH ROW                             --行级别的触发器
   WHEN(NEW.comm>OLD.comm)               --触发体执行的条件
DECLARE
   v_comm   NUMBER;                        --语句块的声明区
BEGIN
   IF UPDATING ('comm') THEN                 --使用条件谓词判断是否是comm列被更新
      v_comm := :NEW.comm - :OLD.comm;      --记录工资的差异
      DELETE FROM emp_history 
            WHERE empno = :OLD.empno;      --删除emp_history中旧表记录
     INSERT INTO emp_history              --向表中插入新的记录
                 VALUES (:emp_old.empno, :emp_old.ename, :emp_old.job, :emp_old.mgr, :emp_old.hiredate,
                        :emp_old.sal, :emp_old.comm, :emp_old.deptno);
      UPDATE emp_history                     --更新薪资值
         SET comm = v_comm
       WHERE empno = :NEW.empno;
      DBMS_OUTPUT.put_line('已经成功的执行了触发器代码');
   END IF;
END;

--代码15.8 使用语句触发器限制修改
CREATE OR REPLACE TRIGGER t_verify_emptime
   BEFORE INSERT OR DELETE OR UPDATE
   ON emp
BEGIN
   --判断当前操作的日期
   IF (TO_CHAR (SYSDATE, 'DAY') IN ('星期六', '星期日'))
      OR (TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00')
   THEN
      --触发异常，将导致整个事务被回滚。
      raise_application_error (-20001, '不能在非常时间段内操纵emp表');
   END IF;
END;



--代码15.9 在触发器中捕捉异常
CREATE OR REPLACE TRIGGER t_emp_exception
  AFTER INSERT ON emp
  FOR EACH ROW
BEGIN
  --向emp_history中插入一条记录
  INSERT INTO emp_history (
    empno, ename, job, sal, hiredate, deptno
  ) 
  VALUES (
    :NEW.empno,:NEW.ename, :NEW.job, :NEW.sal, SYSDATE, :NEW.deptno
  );
EXCEPTION
  WHEN OTHERS THEN           --如果插入的过程中产生任何的异常
    --向日志表中插入一条信息
    INSERT INTO emp_log (log_id, log_action, log_date, empno)
    VALUES (emp_seq.NEXTVAL, '插入员工赵子龙失败', NULL,:NEW.empno);
    --并输出插入失败信息
    DBMS_OUTPUT.put_line('插入记录失败，已经记录到日志表');
END;


select * from emp_log;


--代码15.10 在触发器中使用自治事务
CREATE OR REPLACE TRIGGER t_emp_comm
   BEFORE UPDATE ON emp                   --在UPDATE语句前在emp表上触发
   FOR EACH ROW                             --行级别的触发器
   WHEN(NEW.comm>OLD.comm)               --触发器条件
DECLARE   
   v_comm   NUMBER;                        --语句块的声明区
   PRAGMA AUTONOMOUS_TRANSACTION;    --自治事务      
BEGIN
   IF UPDATING ('comm') THEN                  --使用条件谓词判断是否是comm列被更新
      v_comm := :NEW.comm - :OLD.comm;       --记录提成的差异
      DELETE FROM emp_history 
            WHERE empno = :OLD.empno;        --删除emp_history中旧表记录
      INSERT INTO emp_history                  --向表中插入新的记录
           VALUES (:OLD.empno, :OLD.ename, :OLD.job, :OLD.mgr, :OLD.hiredate,
                   :OLD.sal, :OLD.comm, :OLD.deptno);
      UPDATE emp_history                       --更新提成值
         SET comm = v_comm
       WHERE empno = :NEW.empno;
   END IF;
   COMMIT;                                     --提交结束自治事务
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;                               --发生任何异外回滚自治事务
END;