Oracle笔记十二：触发器

1、触发器简介

• 触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。
• ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。ORACLE将触发器的功能扩展到了触发ORACLE，如数据库的启动与关闭等。所以触发器常用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或用来监视对数据库的各种操作，实现审计的功能。

2、不同类型触发器实例

2.1、DML触发器

2.1.1、当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去

create table emp_hi as select * from empp where 1=2;
create or replace trigger tr_del_empp
 before delete --指定触发时机为删除操作前触发
 on empp
 for each row  --说明创建的是行级触发器 
begin
  --将修改前数据插入到日志记录表 del_emp ,以供监督使用
  insert into emp_hi(empno,ename,job,mgr,hiredate,sal,comm,deptno)
  /*insert只会有new，代表着要插入的新记录

  delete只会有old，代表着要删除的记录

  update由于执行的是先删除旧的记录，再插入新的记录，因此new和old都会有，且含义与上面的相同

  注：update触发器，可根据具体需求选择记录旧记录还是新记录。*/
    values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
end;
-------------------------------------
delete empp where empno=37807;
select * from emp_hi;
drop table emp_hi;
drop trigger tr_del_empp;

2.1.2、限制对Departments表修改

--（包括INSERT,DELETE,UPDATE）的时间范围，即不允许在非工作时间修改departments表
create or replace trigger tr_dept_time
 before insert or delete or update 
 on dept
begin 
  --raise_application_error( error_number_in IN NUMBER, error_msg_in IN VARCHAR2)自定义异常
  --error_number_in范围-20000 到 -20999 之间，这样就不会与 ORACLE 的任何错误代码发生冲突。
  --error_msg_in 的长度不能超过 2k，否则截取 2k。
  if (to_char(sysdate,'day') in ('星期六','星期日')) or (to_char(sysdate,'hh24:mi') 
    not between '08:30' and '18:00') then raise_application_error(-20001,'不是上班时间，不能修改dept表');
  end if;
end;
-------------------------------------
delete from dept where deptno=40;
drop trigger tr_dept_time;

2.1.3、限定只对部门号为101的记录进行行触发器操作

create or replace trigger tr_empp_sal_comm
 before update of sal,comm
  or delete
 on empp
 for each row 
   when (old.deptno=101)
     begin
       case when updating('sal') then 
         if :new.sal < :old.sal then
           raise_application_error(-20001,'部门101的人员的工资不能降');
         end if;
       when updating('comm') then 
         if :new.comm < :old.comm then
           raise_application_error(-20002,'部门101的人员的奖金不能降');
         end if;
       when deleting then
         raise_application_error(-20003,'不能删除部门101的人员记录');
       end case;
     end;
-------------------------------------
update empp set comm=6000 where empno=37805;
delete from empp where empno=37803;
drop trigger tr_empp_sal_comm;

2.1.4、利用行触发器实现级联更新

/*。在修改了主表regions中的region_id之后（AFTER），
级联的、自动的更新子表countries表中原来在该地区的国家的region_id。*/
create or replace trigger tr_reg_cou
after update of deptno
on dept
for each row
begin
  dbms_output.put_line('旧的deptno值是：'||:old.deptno
         ||'、新的deptno值是：'||:new.deptno);
  update emp set deptno=:new.deptno
  where deptno=:old.deptno;
end;
-------------------------------------
update dept set deptno=60 where dname='SALES';
select * from emp;
drop trigger tr_reg_cou;

2.1.5、在触发器中调用过程

create or replace procedure add_emp
(p_emp_id emp.deptno%type,
 p_start_date emp.sal%type,
 p_end_date emp.ename%type
)
is 
begin
  insert into emp(deptno,sal,ename)
  values(p_emp_id,p_start_date,p_end_date);
end;
--创建触发器调用存储过程
create or replace trigger update_emp
 after update of sal
 on empp
 for each row
begin
  add_emp(:old.deptno,:old.sal,:old.ename);
end;
-------------------------------------
update empp set sal=6500 where empno=