触发器应用 trigger

首先有一张表：

create table T_SALARY

(

  name   VARCHAR2(20),

  age    NUMBER(2),

  salary NUMBER(5)

);

insert into t_salary (NAME, AGE, SALARY)

values ('wyl', 23, 63000);

insert into t_salary (NAME, AGE, SALARY)

values ('werxiao', 43, 6012);

insert into t_salary (NAME, AGE, SALARY)

values ('lisi', 54, 7000);

insert into t_salary (NAME, AGE, SALARY)

values ('zhangsan', 42, 4521);

触发器应用场景1：复杂的安全性检查，如下

　　

CREATE OR REPLACE TRIGGER t_security_t_salary

BEFORE UPDATE

ON t_salary

/*

   触发器应用场景1：复杂的安全检查

   禁止在非工作时间操作表

   周末或者不在9点到18点之间，为非工作时间

*/

BEGIN

  IF(to_char(SYSDATE,'day')IN('星期六','星期日')) OR

  to_number(to_char(sysdate,'hh24')) NOT BETWEEN 9 AND 18 THEN

  --to_number(to_char(sysdate,'hh24')) BETWEEN 9 AND 18 THEN

  --禁止insert 新员工

  raise_application_error('-20002','非工作时间不允许操作这张表');

  END IF;

END;

　　其中 raise_application_error()的第一个参数为 -20999到-20000之间。效果图如下：

实际项目中的实例：

 CREATE OR REPLACE TRIGGER TR_AC02_UPDATE

   BEFORE UPDATE ON AC02

   FOR EACH ROW

 DECLARE

   V_AAE036_MAX NUMBER(8); ---上次变动日期

   V_AKC094     NUMBER(16, 2);

   V_COUNT      NUMBER;

   V_AAE240     NUMBER;

   V_AAE011     SKC89.AAE011%TYPE;

   V_CAZ062     SAC03.CAZ062%TYPE;

   V_BAE007     SAC03.BAE007%TYPE;

   V_BAE001     AC02.BAE001%TYPE;

 BEGIN

   IF UPDATING('BAE001') AND :NEW.BAE001 <> :OLD.BAE001 THEN

     ---如果是职工基本医疗切换社保机构，则做备份记录

     IF :OLD.AAE140 = '' THEN

       ----修改skc81的经办机构 xgy 2014.5.22

       UPDATE SKC81

          SET BAE001 = :NEW.BAE001, AAB001 = :NEW.AAB001

        WHERE AAC001 = :OLD.AAC001;

       BEGIN

         SELECT NVL(AAE240, 0)

           INTO V_AAE240

           FROM SKC81

          WHERE AAC001 = :OLD.AAC001

            AND CAE246 = '';

         SELECT AAE011

           INTO V_AAE011

           FROM AC23

          WHERE AAC001 = :OLD.AAC001

            AND AAE036 =

                (SELECT MAX(AAE036) FROM AC23 WHERE AAC001 = :OLD.AAC001);

       EXCEPTION

         WHEN NO_DATA_FOUND THEN

           V_AAE011 := 0;

       END;

       --select aae011 into v_aae011 from ac23 where bae007 = v_bae007;

       INSERT INTO SKC89

         (CKZ711,

          AAC001,

          AAB001_OLD,

          BAE001_OLD,

          AAB001_NEW,

          BAE001_NEW,

          AAE240,

          AAE036,

          AAE011)

       VALUES

         (SEQ_YBDY_CKZ711.NEXTVAL,

          :NEW.AAC001,

          :OLD.AAB001,

          :OLD.BAE001,

          :NEW.AAB001,

          :NEW.BAE001,

          V_AAE240,

          TO_CHAR(SYSDATE, 'yyyymmddhh24miss'),

          V_AAE011);

     END IF;

     NULL;

   END IF;

   --20131121 lqh

   IF UPDATING('aac008') AND :NEW.AAC008 IN ('', '') THEN

     ---2014.10.10 删除对应的sac15

     DELETE FROM SAC15

      WHERE AAC001 = :OLD.AAC001

        AND AAB001 = :OLD.AAB001

        AND AAE140 = :OLD.AAE140;

     IF :OLD.AAE140 = '' AND :NEW.AAC008 = '' THEN

       UPDATE SIC81

          SET CAE246 = ''

        WHERE AAC001 = :OLD.AAC001

          AND AAE140 = '';

     END IF;

     /* if :old.aae140 = '110' and :new.aac008 = '4' then

       update sic81 set cae246 = '2' WHERE AAC001 = :old.aac001 and aae140 ='110';

     end if; */

     IF :OLD.AAE140 = '' AND :NEW.AAC008 IN ('', '') THEN

       UPDATE SKC81 SET CAE246 = '' WHERE AAC001 = :OLD.AAC001;

     END IF;

   END IF;

   ---修改人员附属信息中的社区ID

   IF UPDATING('aab001') THEN

     UPDATE SAC02 SET CAC561 = :NEW.AAB001 WHERE AAC001 = :OLD.AAC001;

   END IF;

   IF UPDATING('aac008') THEN

     INSERT INTO AC02_BF

       SELECT :OLD.AAZ159,

              :OLD.BAE001,

              :OLD.AAB001,

              :OLD.AAC001,

              :OLD.AAE140,

              :OLD.AAC013,

              :OLD.CAC013,

              :OLD.AAA095,

              :OLD.AAC008,

              :OLD.AAC049,

              :OLD.CAC014,

              :OLD.AAE201,

              0,

              TO_CHAR(SYSDATE, 'yyyymmddhh24miss')

         FROM DUAL;

     INSERT INTO AC02_BF

       SELECT :NEW.AAZ159,

              :NEW.BAE001,

              :NEW.AAB001,

              :NEW.AAC001,

              :NEW.AAE140,

              :NEW.AAC013,

              :NEW.CAC013,

              :NEW.AAA095,

              :NEW.AAC008,

              :NEW.AAC049,

              :NEW.CAC014,

              :NEW.AAE201,

              1,

              TO_CHAR(SYSDATE, 'yyyymmddhh24miss')

         FROM DUAL;

   END IF;

 END TR_AC02_UPDATE;