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