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

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

一、使用数据库触发器

数据库触发器是存储在数据库中、根据发生的事件而执行的一种存储子程序。它与存储过程不同的是，存储过程通过其它程序来启动运行或直接启动运行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。该事件可以是一个DDL操作，如对象的创建、修改或删除；也可以是一个DML操作，如表或视图上的一个INSERT、UPDATE或DELETE操作；也可以是系统事件，如数据库启动和关闭、登录和登出；还可以是一个用户事件，如模式登录和登出。
数据库触发器使您能够执行多种函数。

由于入库操作或者出库操作都可能影响到商品的库存量，
所以可以考虑在入库表或者出库表增加一个触发器，当对入库表
或者出库表做DML操作（INSERT、UPDATE、DELETE）
操作时，都会激活入库表或者出库表上的触发器，
进而通过触发器中的逻辑修改库存表中的库存量。

二、要完整实现这一过程，需要理解两个概念：
1、数据库中的事务处理
2、JAVA JDBC调用过程

三、最常见的数据库触发器用法是：
  1、为DDL和DML操作进行审计
  2、为强制执行复杂验证规则，防止错误的或不一致的数据输入到数据库中。
  3、当特定行为发生时，执行与其相关的行为。
  4、强制执行复杂的数据完整性关系。在某些情况下，如父记录更新以后子记录上要执行级联更新操作等。如果不使用数据库触发器，将无法公开的指定这种完整新的约束关系。

  5、自动生成派生值。
  6、处理系统事件。

四、数据库触发器是一种响应数据库事件而执行的存储过程。该事件被称为触发器事件，它可以为下面任意一个事件：
  1、DML操作
  2、一个系统事件，如数据库STARTUP、SHUTDOWN、SERVERERROR
  3、一个用户事件，如LOGON、LOGOFF
触发器事件是在执行触发器语句时进行初始化的。触发器事件在数据库启动与关闭或者用户登录与登出时也能进行初始化

--1：
--试验语句级前后触发器的时机以及行级触发器的时机还有他们分别的触发频率。

-- 1-语句级前后触发器的时机
create or replace trigger tri_em_ta_b   --before
  before insert or update or delete on emp2  
  
declare
begin
  dbms_output.put_line('语句级---前---触发器时机');
end tri_em_ta_b;

create or replace trigger tri_em_ta_a
  after insert or update or delete on emp2  
  
declare
begin
  dbms_output.put_line('语句级---后---触发器时机');
end tri_em_ta_a;

--2- 行级触发器的时机
create or replace trigger tir_emp2_b_up   -- before
  before update on emp2  
  for each row
declare
begin
  dbms_output.put_line('行级---前---触发器---->update');
end tir_emp2_b_up;       
  
create or replace trigger tir_emp2_a_up   -- after
  after update on emp2  
  for each row
declare
begin
   dbms_output.put_line('行级---后---触发器---->update');
end tir_emp2_a_up;

-- 效果：
SQL> update emp2 set ename=upper(ename) where deptno=20;
语句级---前---触发器时机
行级---前---触发器---->update
行级---后---触发器---->update
行级---前---触发器---->update
行级---后---触发器---->update
行级---前---触发器---->update
行级---后---触发器---->update
行级---前---触发器---->update
行级---后---触发器---->update
行级---前---触发器---->update
行级---后---触发器---->update
语句级---后---触发器时机
5 rows updated

--2：
--定义语句级触发器，规定休息日不能对dept表进行修改和新建，但是可以删除
create or replace trigger tir_e_t2_b
  before insert or delete or update on emp2  
  
declare
   v_day varchar2(10);
begin
  select to_char(sysdate,'day') into v_day from dual;
  
  if v_day = '星期六' and v_day = '星期日'
     then
    if updating then
      raise_application_error(-20010,'不能对emp2表进行更新操作');
      
     elsif inserting then
        raise_application_error(-20021,'不能对emp2表进行新建操作');
      end if;
   end if;
end tir_e_t2_b;

--3：
--定义行级触发器，让emp表在修改工资或者修改奖金时，不能超过原来的20%。
--如果出现增长过高，则自动调整为工资增长10%，奖金增长10%
--如果出现工资或者奖金调整后总额小于之前的总额，报错，终断此次事物，并提示总额只能涨不能降。

-- 建表 emp3
   create table emp3(
     empno number primary key,
     sal number,
     comm number,
     foreign key(empno) references emp2(empno)   
   );
   
   create table emp4 as select empno,ename,sal,comm from emp;
   create table emp5 as select * from emp;
   
   alter table emp4 add constraints PK_EMPNO primary key(ename);
    alter table emp5 add constraints PK_ENO primary key(empno);
    
   update emp4 set sal=1000 where ename='ALLEN'; 
    
   create table emp3 is select empno,ename,sal,comm from emp;
   --alter table emp4 add constraints FK_EMPNO foreign key(ename) references emp5(ename); 
   
   alter table emp5 add constraints FK_ENAME foreign key(ename) references emp4(ename); 
    
   flashback table emp3 to  before drop;
   
-- 定义包
create or replace package pak_trim_emp
is
    type t_empno is table of emp.empno%type;
    v_empno t_empno;
end pak_trim_emp;
 
-- 定义行级触发器
create or replace trigger tri_e_ta2_b
  before update  on emp  
  for each row
declare
    eno  number;
begin
  select empno into eno from emp;
  eno:=:old.empno;
end tri_e_ta2_b;

-- 定义行级触发器   --

create or replace trigger tri_e_ta_b
  before update on emp2
  for each row
declare
  v_sal number(7,2);
  v_comm  number(7,2);

begin
  select sal,comm into v_sal,v_comm from emp3 where empno=:new.empno;
  
  if updating then 
    if :new.sal > v_sal*1.2 and nvl(:new.comm,0) >nvl(v_comm,0)*1.2
     then
        --:new.sal:=v_sal*1.1; 
        update emp3 set sal=v_sal*1.1 where empno=:new.empno;
        update emp3 set comm=v_comm*1.1 where empno=:new.empno;
        --:new.comm:=nvl(v_comm,0)*1.1;
        dbms_output.put_line('----------------------');
        
    elsif  :new.sal > v_sal*1.2 and nvl(:new.comm,0) <nvl(v_comm,0)*1.2 and nvl(:new.comm,0) >nvl(v_comm,0)
       then
        --:new.sal:=v_sal*1.1;
        update emp3 set sal=v_sal*1.1 where empno=:new.empno;
        dbms_output.put_line('======================');
        
    elsif :new.sal <v_sal*1.2 and nvl(:new.comm,0) >nvl(v_comm,0)*1.2 and :new.sal >v_sal
       then  
        --:new.comm:=nvl(v_comm,0)*1.1;
        update emp3 set comm=v_comm*1.1 where empno=:new.empno;
        dbms_output.put_line('**********************');
        
    elsif :new.sal <v_sal*1.2 and nvl(:new.comm,0) >nvl(v_comm,0)*1.2 and :new.sal < v_sal
       then  
        --:new.comm:=nvl(v_comm,0)*1.1;
        update emp3 set comm=v_comm*1.1 where empno=:new.empno;
        dbms_output.put_line('**********************');
            
    elsif  :new.sal <v_sal and nvl(:new.comm,0) <nvl(v_comm,0)
      then
        raise_application_error(-20030,'总额只能涨不能降');
    end if; 
  end if;
end tri_e_ta_b;