触发器

 触发器
    触发器是指存放在数据库中，并被隐含执行的存储过程。在8i之前，只允许基于表或视图的DML操作（INSERT,UPDATE和DELETE）建立触发器；
    从8i开始，不仅支持DML出发前哦，也允许基于系统事件（启动数据库，关闭数据库，登陆）和DDL操作建立触发器。
触发器简介：
   触发器由触发事件，触发条件（可选），和触发操作三部分组成。
DML触发器：
    简历DML触发器时，需要指定触发时机（BEFORE OR AFTER）、触发事件（INSERT,UPDATE,DELETE）、表名，触发类型（可选），触发条件（可选）、触发操作。
    CREATE [OR REPLACE] TRIGGER trigger_name
    {BEFORE|AFTER} triggering_event ON table_reference
    [ FOR EACH ROW [WHEN trigger_condition] ]
    trigger_body
    触发操作：
    [DECLARE]
    
    BEGIN
   
    EXCEPTION
   
    END;
   
    触发器的执行顺序：
           如果存在，执行语句级别之前的触发器（语句触发器 不加FOR EACH ROW）
            对于受语句影响的每一行：
                如果存在，执行行级别之前的触发器（行触发器 FOR EACH ROW）
                执行语句本身。
                如果存在，执行行级别之后的触发器
            如果存在，执行语句级别之后的触发器
      语句触发器
         如果对表建立了语句触发器，当对该表进行DML操作时，不管该操作影响了多好行，语句触发器之执行一次。
         当审计DML操作，或者确保DML操作安全时，可使用。
         语句谓词：
         INSERTING : 当进行INSERT 操作时
         UPDATING : 当触发事件是UPDATE操作时
         DELETING : 当触发事件是DELETING 操作时
      建立BEFORE 语句触发器的例子：不能在周末对表EMP 进行DML操作：
          CREATE OR REPLACE TRIGGER tr_sec_emp
     BEFORE INSERT OR UPDATE OR DELETE ON emp
     BEGIN
      IF to_char(sysdate,'DY','nls_date_language=AMERICAN') IN ('SAT','SUN') THEN
        IF INSERTING THEN
           RAISE_APPLICATION_ERROR(-20003,'不能在周末新增雇员');
        ELSIF UPDATING THEN
            RAISE_APPLICATION_ERROR(-20003,'不能在周末更新雇员'); 
        ELSIF DELETING THEN
            RAISE_APPLICATION_ERROR(-20003,'不能在周末删除雇员'); 
        END IF;
      END IF;
     
     END; 
   建立AFTER语句触发器：统计对emp表进行DML操作的次数
    先建表：
      CREATE TABLE audit_table(name varchar2(10),ins integer,upd integer,del integer,startdate date,enddate,date); 
   CREATE OR REPLACE TRIGGER tr_sec_emp
   AFTER INSERT OR UPDATE OR DELETE ON emp
   DECLARE
   v_temp int;
   BEGIN
     SELECT COUNT(1) INTO v_temp FROM audit_table WHERE name='emp';
     IF v_temp=0 THEN
       INSERT INTO audit_table VALUES('emp',0,0,0,sysdate,null);
     END IF;
     CASE  
      WHEN INSERTING THEN
          UPDATE audit_table SET ins=ins+1 enddate=sysdate where name='emp';
      WHEN UPDATING THEN
          UPDATE audit_table SET upd=upd+1 enddate=sysdate where name='emp';
      WHEN DELETING THEN
          UPDATE audit_table SET del=del+1 enddate=sysdate where name='emp';
     END CASE;
   END; 
   行触发器：
      如果对表建立了行触发器，当对该表进行DML操作时，对于影响的每一行，都要执行触发器一次。
   建立DML行级BEFORE触发器：对emp的sal列更新时保证 sal不能小于原来的工资
     CREATE OR REPLACE TRIGGER tr_sec_emp
    BEFORE  UPDATE OF sal ON emp
    FOR EACH ROW
    BEGIN
      IF :NEW.sal<:OLD.sal THEN
         RAISE_APPLICATION_ERROR(-20003,'你有没有良心？工资不能降~');
      END IF;  
    END;
    建立DML AFTER行级触发器：对工资的修改情况进行记录      
      CREATE TABLE audit_emp_change(
        name VARCHAR2(10), oldsal NUMBER(6,2),
        newsal NUMBER(6,2),time DATE
      );
     
       CREATE OR REPLACE TRIGGER tr_sec_emp
     AFTER  UPDATE OF sal ON emp
     FOR EACH ROW
     DECLARE
     v_temp int;
     BEGIN
       SELECT COUNT(1) INTO v_temp FROM audit_emp_change WHERE name=:old.ename;
       IF v_temp=0 THEN
         INSERT INTO audit_emp_change VALUES(:old.ename,:old.sal,:new.sal,sysdate);
       ELSIF
         UPDATE audit_emp_change SET oldsal=:old.sal ,newsal=:new.sal,  time=sysdate where name=:old.ename;
       END IF;   
     END; 
    限制行触发器：可以用WHEN字句加限制 例如在FOR EACH ROW 后加 WHEN (:old.job='SALESMAN')
       限制只对销售部门的员工 修改 工资时触发。 
     注意：使用DML触发器的时候不能从触发器所对应的基表中查询数据，在触发器体中不能有对基表的查询语句（操作）。
  使用DML触发器：
     为了确保数据库满足特定的商业规则或企业逻辑，可以使用约束、触发器和子程序实现。因为约束性能最好，实现最简单，所以首选约束；如果约束不能实现特定规则，那应该选择触发器；如果触发器任然不能实现特定规则，那么应该选子程序。
     1、控制数据安全。
     2、实现数据统计
        ORACLE本身提供了审计功能，例如，如果要对EMP表上的DML操作进行审计，可以执行如下命令
        AUDIT INSERT,UPDATE,DELETE ON emp BY ACCESS；
        如果执行了DML操作，ORACLE将关于sql操作的信息（用户时间等）写入数据字典中。
        而要记载数据便便，则要简历DML触发器了。
     3、实现数据完整性
        为了限制雇员工资不能低于800元。可以选用CHECK约束：
          ALTER TABLE emp ADD CONSTRAINT ck_sal CHECK(sal>=800);
        但是要实现更为复杂的验证，比如不能低于原工资，不能高于原工资的20%等就需要写触发器了。
     4、实现参照完整性
        实现级联删除可用约束：
        ALTER TABLE emp ADD   CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno) ONDELETE CASCADE;
        但是级联更新就要写触发器了。
INSTEAD OF 触发器 （针对视图）
     对于简单视图，可以直接执行DML操作，但是对于复杂视图，不允许直接执行DML操作。具体情况如下：
     1、具有集合操作符（UNION,UNION ALL,INTERSECT,MINUS）;
     2、具有分组函数（SUM,AVG,MIN,MAX,COUNT），
     3、具有GROUP BY,CONNECT BY,STARTWITH
     4、具有DISTINCT 关键字
     5、具有连接查询
     为了对上述复杂视图进行DML操作。必须建立 INSTEAD OF  触发器
     1、INSTEAD OF 触发器只适用于视图
     2、当基于视图建立触发器时，不能指定BEFORE和AFTER选项
     3、在简历视图时没有指定 WITH CHECK OPTION 选项
     4、在建立 INSTEAD OF触发器时，必须指定FOR EACH ROW 选项。
     建立复杂视图：
       CREATE OR REPLACE VIEW dept_emp AS SELECT a.deptno,a.dname,b.empno,b.ename
       FROM dept a,emp b
       WHERE a.deptno=b.deptno
     现在可以允许查询，但是不允许DML操作
     建立 INSTEAD OF 触发器
       以INSERT 为例
       CREATE OR REPLACE TRIGGER tr_instead_of_dep_emp
       INSTEAD OF insert ON  dept_emp
       FOR EACH ROW
       DECLARE
         v_temp INT;
       BEGIN
         select count(1) INTO v_temp FROM dept WHERE deptno=:new.deptno;
         IF v_temp=0 THEN
          INSERT INTO dep(deptno,dname) VALUES(:new.deptno,:new.dname );
         END IF;
         select count(1) INTO v_temp FROM emp WHERE empno=:new.empno;  
         IF v_temp=0 THEN
          INSERT INTO dep(empno,ename,deptno) VALUES(:new.empno,:new.ename,:new.deptno);
         END IF;
       END;
系统级触发器：略过~
管理触发器：
  显示触发器信息：  SELECT trigger_name,status FROM user_triggers WHERE table_name='EMP'
  禁止触发器：ALTER TRIGGER tr_check_sal DISABLE;
  激活触发器：ALTER TRIGGER tr_check_sal ENABLE;   
  禁止活激活表的所有触发器：ALERT TABLE emp DISABLE ALL TRIGGERS;
                            ALERT TABLE emp ENABLE ALL TRIGGERS;