oracle 触发器 (trigger)

/* oracle 触发器 (trigger) */

/*  触发器；是指存放在数据库中并被隐含执行的存储过程  
            可以基于表或视图建立DML（insert ,update ,delete ）触发器，也可以基于
            系统事件（启动数据库，关闭数据库，登录）以及DDL（create ,alter ,drop）建立触发器。
           注意条件；
             1.触发器的大小不能超过32k;
            
             
             
        《一》DML触发器
             1.语句触发器(before)
             create or replace trigger tr_emp
                  before insert or update or delete on emp
              begin
                  raise_application_error( -20101,'正准备插入一条数据!');
             end;
             /
            ==================================================================
            --.使用条件谓词（inserting,updating,deleting）
               create or replace trigger tr_emp
                  before insert or update or delete on emp
              begin
                 case
                   when inserting then
                        raise_application_error( -20101,'正准备插入一条数据!');
                   when updating then
                        raise_application_error( -20101,'正准备更新一条数据!');
                   when deleting then
                        raise_application_error( -20101,'正准备删除一条数据!');
                 end case;
              
             end;
             /
             ===========================================================================
             2.语句触发器(after)(一般用于统计数据或汇总数据)
                  create or replace trigger tr_emp
                  before insert or update or delete on emp
              begin
                 case
                   when inserting then
                       .........
                   when updating then
                       ............
                   when deleting then
                      .............
                 end case;
              
             end;
             ======================================================
             
            3.使用行触发器(before)（执行DML操作时，每作用一行就会触发一次）     
             create or replace trigger tr_emp
                  before insert or update or delete of sal on emp
                  for each row
               begin
                 case
                   when updating then
                      if :old.sal>:new.sal then
                        raise_application_error( -20101,'工资只涨不降!');
                      end if;
                 end case;
             end;

            =================================================================
            
            4.使用行触发器(alter)（执行DML操作时，每作用一行就会触发一次）
             create or replace trigger tr_emp
                  alter insert or update or delete of sal on emp
                  for each row
               begin
                 ....
             end;
             
             
          ====================================================================
          
          5.限制行触发器（when 子句）触发特定行
            create or replace trigger tr_emp
                  before  update of sal on emp
                  for each row
                  when(old.name='bb') --只触发name='bb'的员工
                begin
                 case
                   when updating then
                      if :old.sal>:new.sal then
                        raise_application_error( -20101,'工资只涨不降!');
                      end if;
                 end case;

             end;

          
          
         1> 使用DML触发器：
             为了确保数据库数据满足特定的商业规则或企业逻辑，可以使用约束，触发器和子程序实现；因为约束
             实现最简单，性能最好，所以首选约束，如果使用约束不能实现那么在选择触发器，如果触发器仍然不
             能实现特定规则，那么应该选择子程序（过程或函数），DML 可以用于实现数据安全保护，数据审计，数据完整性，
             数据恢复等功能、
          
          如；实现数据安全性(更加精确的控制)
               create or replace trigger tr_emp
                  before  update of sal on emp
                  for each row
                 -- when(old.name='bb')
             begin
               
               if to_char(sysdate,'HH24') not between '14' and '17' then  --控制只能在时间段才能更新数据
                  raise_application_error(-20201,'只能在 14点到18点之间更新数据!');
               end if ;
              
                case
                   when updating then
                      if :old.sal>:new.sal then
                        raise_application_error( -20101,'工资只涨不降!');
                      end if;
                 end case;

             end;
          
          2> DML 触发器可以用于实现数据完整性 和 参照完整性
             某些情况下无法用约束实现商业规则，则可以使用触发器，有些时候使用约束只能
              如: 级联删除on delete cascade  可以用约束，但是如果更新主表的时候也想更新从表则必须用触发器
                create or replace trigger tr_emp
                  after  update of deptno on dept
                for each row
                  
                  begin
                    update emp p set p.deptno = :new.deptno
                          where p.deptno = :old.deptno;

                  end;

          
          ===============================================================================================================
          
          《二》 建立instead of 触发器（处理复杂视图）
                   对于简单视图可以直接使用 insert ,update,delete 操作，但是对于复杂视图则不允许这样；
                    如；
                     1》具有集合操作符（union ,union all ,intersect ,minus..）
                     2》具有分组函数（min,max,sum,avg,count..）
                     3》具有(group by ,connect by ,start with)
                     4》distinct 关键字
                     5》具有连接查询
             
          
               适用范围；
                   1》只适用与视图
                   2》当建立视图触发器时不能指定 before,after 选项
                   3》没有with check option
                   4》当建立instead of 触发器时必须使用 for each row
          
          
            eg;

             create or replace view v_emp_dept as
                 select a.empname,a.deptno ,b.deptname,b.deptno
                    from emp a,dept b
                       where a.deptno = b.deptno;

                create or replace trigger tr_emp_dept
                             instead of  insert on v_emp_dept
                             for each row
                 declare
                        v_count number(10);        
                begin
                        select count(*) into v_count from emp where deptno = :new.empno;
                         if v_count = 0 then
                            insert into emp values(;new.empname,:new.deptno);
                         end if;
                          
                         select count(*) into v_count from dept where deptno = :new.deptno;
                           if v_count = 0 then
                              insert into dept values(:new.empname,:new.deptno,:new.empno);
                           end if;
                 end;
                          
          
         =================================================================================================
         
         《三》建立系统事件触发器
              通过使用系统事件触发器，提供了跟踪系统或数据库变化的机制；
           
            --create table
            create table event_table(event varchar2(30),time date);

            -- create trigger
            -- 例程启动只能用after
            create or replace trigger tr_event
              after startup on database
              begin
                insert into u1.event_table values(ora_sysevent,sysdate);--ora_sysevent(用于返回触发器事件属性名)
              end;

            --关闭例程只用于before
            create or replace trigger tr_event
              before shutdown on database
              begin
                insert into u1.event_table values(ora_sysevent,sysdate);--ora_sysevent(用于返回触发器事件属性名)
              end;  
              
         
            -- 还可以建立 登入（logon）, 退出（logoff）,DDL事件触发器（比如；可以详细记载创建的人，对象....）
              .....
              
              
           《4》管理触发器
                1.查看相关触发器信息；
                   select tr.trigger_name,tr.status,tr.table_owner from user_triggers tr ;
               
                2. 可以禁用触发器
                   alter trigger tr_event disable;
                   
                3.激活触发器
                  alter trigger tr_event enable;   
                          
                4.重新编译触发器
                  alter trigger tr_event compile;
                
                5.删除触发器
                  drop trigger tr_event;
          
          
             
*/