oracle觸發器(trigger)的使用示例

建立一個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;