8.5 数据库触发器的应用举例
例1：创建一个DML语句级触发器，当对emp表执行INSERT, UPDATE, DELETE 操作时，它自动更新dept_summary 表中的数据。由于在PL/SQL块中不能直接调用DDL语句，所以，利用ORACLE内置包DBMS_UTILITY中的EXEC_DDL_STATEMENT过程，由它执行DDL语句创建触发器。
CREATE TABLE dept_summary( Deptno NUMBER(2), Sal_sum NUMBER(9, 2), Emp_count NUMBER);
INSERT INTO dept_summary(deptno, sal_sum, emp_count) SELECT deptno, SUM(sal), COUNT(*) FROM emp
GROUP BY deptno;
--创建一个PL/SQL过程disp_dept_summary
--在触发器中调用该过程显示dept_summary标中的数据。 CREATE OR REPLACE PROCEDURE disp_dept_summary IS
Rec dept_summary%ROWTYPE;
CURSOR c1 IS SELECT * FROM dept_summary; BEGIN OPEN c1;
FETCH c1 INTO REC;
DBMS_OUTPUT.PUT_LINE('deptno sal_sum emp_count'); DBMS_OUTPUT.PUT_LINE('-------------------------------------'); WHILE c1%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(rec.deptno, 6)|| To_char(rec.sal_sum, '$999,999.99')|| LPAD(rec.emp_count, 13)); FETCH c1 INTO rec; END LOOP; CLOSE c1; END; BEGIN
DBMS_OUTPUT.PUT_LINE('插入前'); Disp_dept_summary();
DBMS_UTILITY.EXEC_DDL_STATEMENT(' CREATE OR REPLACE TRIGGER trig1
AFTER INSERT OR DELETE OR UPDATE OF sal ON emp BEGIN
DBMS_OUTPUT.PUT_LINE(''正在执行trig1 触发器…''); DELETE FROM dept_summary;
INSERT INTO dept_summary(deptno, sal_sum, emp_count) SELECT deptno, SUM(sal), COUNT(*) FROM emp GROUP BY deptno; END; ');
INSERT INTO dept(deptno, dname, loc)
VALUES(90, ‘demo_dept’, ‘none_loc’); INSERT INTO emp(ename, deptno, empno, sal) VALUES(USER, 90, 9999, 3000);
DBMS_OUTPUT.PUT_LINE('插入后'); Disp_dept_summary();
UPDATE emp SET sal=1000 WHERE empno=9999; DBMS_OUTPUT.PUT_LINE('修改后'); Disp_dept_summary();
DELETE FROM emp WHERE empno=9999; DELETE FROM dept WHERE deptno=90;
DBMS_OUTPUT.PUT_LINE('删除后'); Disp_dept_summary();
DBMS_UTILITY.EXEC_DDL_STATEMENT(‘DROP TRIGGER trig1’); EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;
例2：创建DML语句行级触发器。当对emp表执行INSERT, UPDATE, DELETE 操作时，它自动更新dept_summary 表中的数据。由于在PL/SQL块中不能直接调用DDL语句，所以，利用ORACLE内置包DBMS_UTILITY中的EXEC_DDL_STATEMENT过程，由它执行DDL语句创建触发器。
BEGIN
DBMS_OUTPUT.PUT_LINE('插入前'); Disp_dept_summary();
DBMS_UTILITY.EXEC_DDL_STATEMENT(
'CREATE OR REPLACE TRIGGER trig2_update AFTER UPDATE OF sal ON emp
REFERENCING OLD AS old_emp NEW AS new_emp FOR EACH ROW
WHEN (old_emp.sal != new_emp.sal) BEGIN
DBMS_OUTPUT.PUT_LINE(''正在执行trig2_update 触发器…''); DBMS_OUTPUT.PUT_LINE(''sal 旧值：''|| :old_emp.sal); DBMS_OUTPUT.PUT_LINE(''sal 新值：''|| :new_emp.sal); UPDATE dept_summary
SET sal_sum=sal_sum + :new_emp.sal - :old_emp.sal WHERE deptno = :new_emp.deptno; END;' );
DBMS_UTILITY.EXEC_DDL_STATEMENT( 'CREATE OR REPLACE TRIGGER trig2_insert AFTER INSERT ON emp
REFERENCING NEW AS new_emp FOR EACH ROW DECLARE I NUMBER; BEGIN
DBMS_OUTPUT.PUT_LINE(''正在执行trig2_insert 触发器…''); SELECT COUNT(*) INTO I
FROM dept_summary WHERE deptno = :new_emp.deptno; IF I > 0 THEN
UPDATE dept_summary
SET sal_sum=sal_sum+:new_emp.sal, Emp_count=emp_count+1
WHERE deptno = :new_emp.deptno; ELSE
INSERT INTO dept_summary
VALUES (:new_emp.deptno, :new_emp.sal, 1); END IF; END;' );
DBMS_UTILITY.EXEC_DDL_STATEMENT( 'CREATE OR REPLACE TRIGGER trig2_delete AFTER DELETE ON emp
REFERENCING OLD AS old_emp FOR EACH ROW DECLARE I NUMBER; BEGIN
DBMS_OUTPUT.PUT_LINE(''正在执行trig2_delete 触发器…''); SELECT emp_count INTO I
FROM dept_summary WHERE deptno = :old_emp.deptno; IF I >1 THEN
UPDATE dept_summary
SET sal_sum=sal_sum - :old_emp.sal, Emp_count=emp_count - 1
WHERE deptno = :old_emp.deptno; ELSE
DELETE FROM dept_summary WHERE deptno = :old_emp.deptno; END IF; END;' );
INSERT INTO dept(deptno, dname, loc) VALUES(90, 'demo_dept', 'none_loc');
INSERT INTO emp(ename, deptno, empno, sal) VALUES(USER, 90, 9999, 3000);
INSERT INTO emp(ename, deptno, empno, sal) VALUES(USER, 90, 9998, 2000); DBMS_OUTPUT.PUT_LINE('插入后'); Disp_dept_summary();
UPDATE emp SET sal = sal*1.1 WHERE deptno=90; DBMS_OUTPUT.PUT_LINE('修改后'); Disp_dept_summary();
DELETE FROM emp WHERE deptno=90; DELETE FROM dept WHERE deptno=90; DBMS_OUTPUT.PUT_LINE('删除后'); Disp_dept_summary();
DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_update'); DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_insert'); DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_delete'); EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); END;
例3：利用ORACLE提供的条件谓词INSERTING、UPDATING和DELETING创建与例2具有相同功能的触发器。
BEGIN
DBMS_OUTPUT.PUT_LINE('插入前'); Disp_dept_summary();
DBMS_UTILITY.EXEC_DDL_STATEMENT( 'CREATE OR REPLACE TRIGGER trig2
AFTER INSERT OR DELETE OR UPDATE OF sal ON emp
REFERENCING OLD AS old_emp NEW AS new_emp FOR EACH ROW DECLARE
I NUMBER; BEGIN
IF UPDATING AND :old_emp.sal != :new_emp.sal THEN DBMS_OUTPUT.PUT_LINE(''正在执行trig2 触发器…''); DBMS_OUTPUT.PUT_LINE(''sal 旧值：''|| :old_emp.sal); DBMS_OUTPUT.PUT_LINE(''sal 新值：''|| :new_emp.sal); UPDATE dept_summary
SET sal_sum=sal_sum + :new_emp.sal - :old_emp.sal WHERE deptno = :new_emp.deptno; ELSIF INSERTING THEN
DBMS_OUTPUT.PUT_LINE(''正在执行trig2触发器…''); SELECT COUNT(*) INTO I FROM dept_summary
WHERE deptno = :new_emp.deptno; IF I > 0 THEN
UPDATE dept_summary SET sal_sum=sal_sum+:new_emp.sal, Emp_count=emp_count+1
WHERE deptno = :new_emp.deptno; ELSE
INSERT INTO dept_summary
VALUES (:new_emp.deptno, :new_emp.sal, 1); END IF; ELSE
DBMS_OUTPUT.PUT_LINE(''正在执行trig2触发器…''); SELECT emp_count INTO I
FROM dept_summary WHERE deptno = :old_emp.deptno; IF I > 1 THEN
UPDATE dept_summary
SET sal_sum=sal_sum - :old_emp.sal, Emp_count=emp_count - 1
WHERE deptno = :old_emp.deptno; ELSE
DELETE FROM dept_summary WHERE deptno = :old_emp.deptno; END IF; END IF;
数据库触发器的应用实例
