oracle触发器(trigger)的使用示例

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;