触发器应用 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之间。效果图如下：

实际项目中的实例：

  1 CREATE OR REPLACE TRIGGER TR_AC02_UPDATE
  2   BEFORE UPDATE ON AC02
  3   FOR EACH ROW
  4 DECLARE
  5 
  6   V_AAE036_MAX NUMBER(8); ---上次变动日期
  7   V_AKC094     NUMBER(16, 2);
  8   V_COUNT      NUMBER;
  9   V_AAE240     NUMBER;
 10   V_AAE011     SKC89.AAE011%TYPE;
 11   V_CAZ062     SAC03.CAZ062%TYPE;
 12   V_BAE007     SAC03.BAE007%TYPE;
 13   V_BAE001     AC02.BAE001%TYPE;
 14 BEGIN
 15 
 16   IF UPDATING('BAE001') AND :NEW.BAE001 <> :OLD.BAE001 THEN
 17     ---如果是职工基本医疗切换社保机构，则做备份记录
 18     IF :OLD.AAE140 = '310' THEN
 19       ----修改skc81的经办机构 xgy 2014.5.22
 20       UPDATE SKC81
 21          SET BAE001 = :NEW.BAE001, AAB001 = :NEW.AAB001
 22        WHERE AAC001 = :OLD.AAC001;
 23       BEGIN
 24         SELECT NVL(AAE240, 0)
 25           INTO V_AAE240
 26           FROM SKC81
 27          WHERE AAC001 = :OLD.AAC001
 28            AND CAE246 = '1';
 29       
 30         SELECT AAE011
 31           INTO V_AAE011
 32           FROM AC23
 33          WHERE AAC001 = :OLD.AAC001
 34            AND AAE036 =
 35                (SELECT MAX(AAE036) FROM AC23 WHERE AAC001 = :OLD.AAC001);
 36       EXCEPTION
 37         WHEN NO_DATA_FOUND THEN
 38           V_AAE011 := 0;
 39       END;
 40       --select aae011 into v_aae011 from ac23 where bae007 = v_bae007;
 41       INSERT INTO SKC89
 42         (CKZ711,
 43          AAC001,
 44          AAB001_OLD,
 45          BAE001_OLD,
 46          AAB001_NEW,
 47          BAE001_NEW,
 48          AAE240,
 49          AAE036,
 50          AAE011)
 51       VALUES
 52         (SEQ_YBDY_CKZ711.NEXTVAL,
 53          :NEW.AAC001,
 54          :OLD.AAB001,
 55          :OLD.BAE001,
 56          :NEW.AAB001,
 57          :NEW.BAE001,
 58          V_AAE240,
 59          TO_CHAR(SYSDATE, 'yyyymmddhh24miss'),
 60          V_AAE011);
 61     END IF;
 62     NULL;
 63   END IF;
 64 
 65   --20131121 lqh
 66   IF UPDATING('aac008') AND :NEW.AAC008 IN ('2', '4') THEN
 67     ---2014.10.10 删除对应的sac15
 68     DELETE FROM SAC15
 69      WHERE AAC001 = :OLD.AAC001
 70        AND AAB001 = :OLD.AAB001
 71        AND AAE140 = :OLD.AAE140;
 72     IF :OLD.AAE140 = '110' AND :NEW.AAC008 = '2' THEN
 73       UPDATE SIC81
 74          SET CAE246 = '1'
 75        WHERE AAC001 = :OLD.AAC001
 76          AND AAE140 = '110';
 77     END IF;
 78     /* if :old.aae140 = '110' and :new.aac008 = '4' then
 79       update sic81 set cae246 = '2' WHERE AAC001 = :old.aac001 and aae140 ='110';
 80     end if; */
 81     IF :OLD.AAE140 = '310' AND :NEW.AAC008 IN ('2', '4') THEN
 82       UPDATE SKC81 SET CAE246 = '1' WHERE AAC001 = :OLD.AAC001;
 83     END IF;
 84   
 85   END IF;
 86 
 87   ---修改人员附属信息中的社区ID 
 88   IF UPDATING('aab001') THEN
 89     UPDATE SAC02 SET CAC561 = :NEW.AAB001 WHERE AAC001 = :OLD.AAC001;
 90   END IF;
 91 
 92   IF UPDATING('aac008') THEN
 93     INSERT INTO AC02_BF
 94       SELECT :OLD.AAZ159,
 95              :OLD.BAE001,
 96              :OLD.AAB001,
 97              :OLD.AAC001,
 98              :OLD.AAE140,
 99              :OLD.AAC013,
100              :OLD.CAC013,
101              :OLD.AAA095,
102              :OLD.AAC008,
103              :OLD.AAC049,
104              :OLD.CAC014,
105              :OLD.AAE201,
106              0,
107              TO_CHAR(SYSDATE, 'yyyymmddhh24miss')
108         FROM DUAL;
109     INSERT INTO AC02_BF
110       SELECT :NEW.AAZ159,
111              :NEW.BAE001,
112              :NEW.AAB001,
113              :NEW.AAC001,
114              :NEW.AAE140,
115              :NEW.AAC013,
116              :NEW.CAC013,
117              :NEW.AAA095,
118              :NEW.AAC008,
119              :NEW.AAC049,
120              :NEW.CAC014,
121              :NEW.AAE201,
122              1,
123              TO_CHAR(SYSDATE, 'yyyymmddhh24miss')
124         FROM DUAL;
125   END IF;
126 
127 END TR_AC02_UPDATE;