触发器应用 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;
