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; 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'); EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); END;

例4：创建INSTEAD OF 触发器。首先创建一个视图myview, 由于该视图是复合查询所产生的视图，所以不能执行DML语句。根据用户对视图所插入的数据判断需要将数据插入到哪个视图基表中，然后对该基表执行插入操作。

DECLARE

No NUMBER;

Name VARCHAR2(20); BEGIN

DBMS_UTILITY.EXEC_DDL_STATEMENT(' CREATE OR REPLACE VIEW myview AS

SELECT empno, ename, ''E'' type FROM emp UNION

SELECT dept.deptno, dname, ''D'' FROM dept ');

-- 创建INSTEAD OF 触发器trigger3; DBMS_UTILITY.EXEC_DDL_STATEMENT(' CREATE OR REPLACE TRIGGER trig3 INSTEAD OF INSERT ON myview REFERENCING NEW n FOR EACH ROW DECLARE

Rows INTEGER; BEGIN

DBMS_OUTPUT.PUT_LINE(''正在执行trig3触发器…''); IF :n.type = ''D'' THEN SELECT COUNT(*) INTO rows

FROM dept WHERE deptno = :n.empno; IF rows = 0 THEN

DBMS_OUTPUT.PUT_LINE(''向dept表中插入数据…''); INSERT INTO dept(deptno, dname, loc) VALUES (:n.empno, :n.ename, ''none’’); ELSE

DBMS_OUTPUT.PUT_LINE(''编号为''|| :n.empno|| ''的部门已存在，插入操作失败！''); END IF; ELSE

SELECT COUNT(*) INTO rows

FROM emp WHERE empno = :n.empno; IF rows = 0 THEN

DBMS_OUTPUT.PUT_LINE('’向emp表中插入数据…’’); INSERT INTO emp(empno, ename) VALUES(:n.empno, :n.ename); ELSE

DBMS_OUTPUT.PUT_LINE(''编号为''|| :n.empno|| ''的人员已存在，插入操作失败!''); END IF; END IF; END; ');