增强数据的完整性管理;
例：修改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;
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;
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;
管理复杂的表复制;
防止非法的事务发生;
自动生成派生的列值;
帮助式显复杂的商业管理。
