ORACLE触发器详解 - 数据库编程

d.empno); ELSE INSERT INTO audit_table VALUES(audit_seq.NEXTVAL, user, time_now, terminal, 'EMP', 'UPDATE', :old.empno); IF UPDATING('SAL') THEN INSERT INTO audit_table_val VALUES(audit_seq.CURRVAL, 'SAL', :old.sal, :new.sal); ELSE UPDATING('DEPTNO') INSERT INTO audit_table_val VALUES(audit_seq.CURRVAL, 'DEPTNO', :old.deptno, :new.deptno); END IF; END IF; END;

l 增强数据的完整性管理；

例：修改DEPT表的DEPTNO列时，同时把EMP表中相应的DEPTNO也作相应的修改；

CREATE SEQUENCE update_sequence
INCREMENT BY 1
START WITH 1000
MAXVALUE 5000 CYCLE;

ALTER TABLE emp
ADD update_id NUMBER;

CREATE OR REPLACE PACKAGE integritypackage AS
Updateseq NUMBER;
END integritypackage;

CREATE OR REPLACE PACKAGE BODY integritypackage AS
END integritypackage;

CREATE OR REPLACE TRIGGER dept_cascade1
BEFORE UPDATE OF deptno ON dept
DECLARE
Dummy NUMBER;
BEGIN
SELECT update_sequence.NEXTVAL INTO dummy FROM dual;
Integritypackage.updateseq:=dummy;
END;

CREATE OR REPLACE TRIGGER dept_cascade2
AFTER DELETE OR UPDATE OF deptno ON dept
FOR EACH ROW
BEGIN
IF UPDATING THEN
UPDATE emp SET deptno=:new.deptno,
update_id=integritypackage.updateseq
WHERE emp.deptno=:old.deptno AND update_id IS NULL;
END IF;
IF DELETING THEN
DELETE FROM emp
WHERE emp.deptno=:old.deptno;
END IF;
END;

CREATE OR REPLACE TRIGGER dept_cascade3
AFTER UPDATE OF deptno ON dept
BEGIN
UPDATE emp SET update_id=NULL
WHERE update_id=integritypackage.updateseq;
END;

SELECT * FROM EMP ORDER BY DEPTNO;
UPDATE dept SET deptno=25 WHERE deptno=20;

l 帮助实现安全控制；

例：保证对EMP表的修改仅在工作日的工作时间；

CREATE TABLE company_holidays(day DATE);

INSERT INTO company_holidays
VALUES(sysdate);
INSERT INTO company_holidays
VALUES(TO_DATE('21-10月-01', 'DD-MON-YY'));

CREATE OR REPLACE TRIGGER emp_permit_change
BEFORE INSERT OR DELETE OR UPDATE ON emp
DECLARE
Dummy NUMBER;
Not_on_weekends EXCEPTION;
Not_on_holidays EXCEPTION;
Not_working_hours EXCEPTION;
BEGIN
/* check for weekends */
IF TO_CHAR(SYSDATE, 'DAY') IN ('星期六', '星期日') THEN
RAISE not_on_weekends;
END IF;
/* check for company holidays */
SELECT COUNT(*) INTO dummy FROM company_holidays
WHERE TRUNC(day)=TRUNC(SYSDATE);
IF dummy >0 THEN
RAISE not_on_holidays;
END IF;
/* check for work hours(8:00 AM to 18:00 PM */
IF (TO_CHAR(SYSDATE,'HH24')<8 OR TO_CHAR(SYSDATE, 'HH24')>18) THEN
RAISE not_working_hours;
END IF;
EXCEPTION
WHEN not_on_weekends THEN
RAISE_APPLICATION_ERROR(-20324,
'May not change employee table during the weekends');
WHEN not_on_holidays THEN
RAISE_APPLICATION_ERROR(-20325,
'May not change employee table during a holiday');
WHEN not_working_hours THEN
RAISE_APPLICATION_ERROR(-20326,
'May not change employee table during no_working hours');
END;

ORACLE触发器详解(六)