terminal, 'EMP', 'DELETE', :old.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;
? 增强数据的完整性管理；
例：修改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;
? 帮助实现安全控制；
例：保证对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;