Oracle-trigger触发器

一、触发器：数据库触发器是一个与表、视图相关联的，存储的PL/SQL 语句。（即删表自动删触发器）
    每当一个特定的MDL数据操作语句（insert update delete）在指定的表上发出时，Oracle自动执行触发器中定义的语句序列。

语法：
    CREATE [OR REPLACE] TRIGGER trigger_name
    {BEFORE | AFTER }
    {INSERT | DELETE | UPDATE [OF column [, column …]]}
    [OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
    ON [schema.]table_name | [schema.]view_name
    [REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
    [FOR EACH ROW ]
    [WHEN condition]
    PL/SQL_BLOCK | CALL procedure_name;

二、分    类：前置触发器 后置触发器
        行级    语句级
    如果指定为BEFORE，则表示在执行DML操作之前触发，以便防止某些错误操作发生或实现某些业务规则；
    如果指定为AFTER， 则表示在执行DML操作之后触发，以便记录该操作或做某些事后处理

三、注    意：触发器自动commit，也不能使用rollback等事务操作语言
          既可以是单个触发事件，也可以是多个触发事件的组合（只能使用OR逻辑组合，不能使用AND逻辑组合）。
    
四、应用场景：
    1、数据确认
    2、实施复杂的安全性检查
    3、做审计,跟踪表上的数据操作等
    4、数据同步和同步
应用一：复杂的安全性检查
/**
非工作时间（星球六 星期日， 非9点~18点的区间）
禁止写入数据

首先要搞清楚： 触发器的类型--语句级触发器。
不管插入了多少条数据，没有必要对每一行数据都进行校验，只要不在这个时间段内，都不让插入。
*/
create or replace trigger tri_emp1
  before insert on emp1  

declare
  -- local variables here
begin

  if to_char(sysdate, 'day') in ('星期五', '星期日') or
     to_number(to_char(sysdate, 'hh24')) not between 9 and 18 then
  --禁止insert 
       raise_application_error(-20023,'非工作时间禁止插入数据');        --报错编码自定义-20000 到 -20999 之间，报错内容自定义。
  --DBMS_output.put_line('错误');
  end if;
end tri_emp1;

应用二：数据确认
工资不能越涨越少
分析： 首先确认下触发器的类型,因为需要对每一条数据进行确认,所以是个行级触发器
/**
涨后的薪水不能低于涨前的薪水

1 :old 和 :new 代表同一条记录
2  :old 代表操作该行之前，这一行的值
   :new 代表操作该行之后，这一行的值
*/
create or replace trigger tri_emp1
  before update 
  on emp1  
  for each row
declare
  -- local variables here  没有变量声明的话，declare可以省略
begin

 --- if 涨后的薪水 <  涨前的薪水 then  如何表示呢 ？
  if :new.sal < :old.sal then

    raise_application_error(-20002,'涨后的薪水:'|| :new.sal ||'小于涨前的薪水：'||:old.sal);
  end if;
end tri_emp1;

应用三 数据库审计
创建基于值的触发器
create table info(info varchar2(256)) ;

CREATE or replace trigger tri_info 
after update on emp1 
for each row 
declare 
begin 
    if :new.sal >6000 then 
    insert into info(info) VALUES (:new.sal||'-'||:new.ename||:new.deptno);
    ELSE dbms_output.put_line('需要涨工资');
  end if;
  end ;
 可以同时对一张表进行多个触发器设置，最多12个！
 
应用四 数据的备份和同步
当给员工涨完工资后，自动备份到备份表中
/**
记录信息用after，:old.sal  
**/

CREATE OR REPLACE TRIGGER tri_info
AFTER UPDATE ON emp1
FOR EACH ROW
  DECLARE
  BEGIN
    UPDATE emp1 SET sal=:new.sal WHERE ename=:new.ename;
  END ;
  
建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去

create table emp2 as select * from emp1 where 1=2;

CREATE OR REPLACE TRIGGER tri_emp2
BEFORE DELETE ON emp1
FOR EACH ROW
  DECLARE
  v_empno NUMBER;
  BEGIN
  INSERT INTO emp2(empno,ename) VALUES(:old.empno,:old.ename);  
  END ;
限制对Departments表修改（包括INSERT,DELETE,UPDATE）的时间范围，即不允许在非工作时间修改departments表
CREATE OR REPLACE TRIGGER tr_dept_time
BEFORE INSERT OR DELETE OR UPDATE
ON departments
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, '不是上班时间，不能修改departments表');
 END IF;
END;
限定只对部门号为80的记录进行行触发器操作。
REATE OR REPLACE TRIGGER tr_emp_sal_comm
BEFORE UPDATE OF salary, commission_pct
       OR DELETE
ON HR.employees
FOR EACH ROW
WHEN (old.department_id = 80)
BEGIN
 CASE
     WHEN UPDATING ('salary') THEN
        IF :NEW.salary < :old.salary THEN

           RAISE_APPLICATION_ERROR(-20001, '部门80的人员的工资不能降');
        END IF;
     WHEN UPDATING ('commission_pct') THEN

        IF :NEW.commission_pct < :old.commission_pct THEN
           RAISE_APPLICATION_ERROR(-20002, '部门80的人员的奖金不能降');
        END IF;
     WHEN DELETING THEN
          RAISE_APPLICATION_ERROR(-20003, '不能删除部门80的人员记录');
     END CASE;
END;
/*
实例：
UPDATE employees SET salary = 8000 WHERE employee_id = 177;
DELETE FROM employees WHERE employee_id in (177,170);
*/

利用行触发器实现级联更新。在修改了主表regions中的region_id之后（AFTER），级联的、自动的更新子表countries表中原来在该地区的国家的region_id。
CREATE OR REPLACE TRIGGER tr_reg_cou
AFTER update OF region_id
ON regions
FOR EACH ROW
BEGIN
 DBMS_OUTPUT.PUT_LINE('旧的region_id值是'||:old.region_id
                  ||'、新的region_id值是'||:new.region_id);
 UPDATE countries SET region_id = :new.region_id
 WHERE region_id = :old.region_id;
END;

在触发器中调用过程。
CREATE OR REPLACE PROCEDURE add_job_history
 ( p_emp_id          job_history.employee_id%type
   , p_start_date      job_history.start_date%type
  , p_end_date        job_history.end_date%type
   , p_job_id          job_history.job_id%type
   , p_department_id   job_history.department_id%type
   )
IS
BEGIN
 INSERT INTO job_history (employee_id, start_date, end_date,
                           job_id, department_id)
  VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;

--创建触发器调用存储过程...
CREATE OR REPLACE TRIGGER update_job_history
 AFTER UPDATE OF job_id, department_id ON employees
 FOR EACH ROW
BEGIN
 add_job_history(:old.employee_id, :old.hire_date, sysdate,
                  :old.job_id, :old.department_id);
END;