Oracle触发器

例2：创建DML语句行级触发器。当对emp表执行INSERT,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,loc)

??? VALUES(90,‘demo_dept‘,‘none_loc‘);

? INSERT INTO emp(ename,sal)

??? VALUES(USER,3000);

? INSERT INTO emp(ename,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,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,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;

（编辑：安卓应用网_ASP源码网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!