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

触发器应用 trigger

实际项目中的实例:

 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;
上一篇:C陷阱与缺陷(四)


下一篇:jvm调优经验分享