觸發器儘量考慮內部程式碼過程的封裝（在11g中沒有差別）

LuiseDalian發表於2014-01-16
觸發器封裝

點選(此處)摺疊或開啟

  1. --觸發器執行的程式碼儘量考慮使用儲存過程進行封裝
  2. DROP TABLE t1;
  3. DROP TABLE t2;
  4. CREATE TABLE t1 AS SELECT ename, empno, deptno FROM scott.emp;
  5. CREATE TABLE t2 AS SELECT deptno, count(*) as cnt FROM t1 group by deptno;

  7. CREATE OR REPLACE TRIGGER tri_t1_insert
  8. AFTER INSERT ON t1
  9. FOR EACH ROW
  10. BEGIN
  11.   INSERT INTO t2(deptno, cnt) VALUES(:new.deptno, 1);
  12. END tri_t1_insert;
  13. /

  15. ALTER SESSION SET TRACEFILE_IDENTIFIER = \'LUISE_OPTIMIZER\';
  16. ALTER SESSION SET EVENTS \'10046 trace name context forever, level 8\';
  17. INSERT INTO t1(ename, empno, deptno) VALUES(\'luise\', 666, 8);
  18. INSERT INTO t1(ename, empno, deptno) VALUES (\'luise\', 777, 8);
  19. COMMIT;
  20. ALTER SESSION SET EVENTS \'10046 trace name context off\';

  22. col name for a25
  23. col value for a60
  24. select * from v$diag_info;

[oracle@S1011:/export/home/oracle]$ cd /u01/app/oracle/diag/rdbms/testdb11/TestDB11/trace

[oracle@S1011:/u01/app/oracle/diag/rdbms/testdb11/TestDB11/trace]$ ls *LUISE_OPTIMIZER*.trc

TestDB11_ora_914_LUISE_OPTIMIZER.trc        

 

tkprof TestDB11_ora_914_LUISE_OPTIMIZER.trc 1.txt  sys=no sort=prsela,exeela,fchela

--檢視1.txt確定insert into t2...語句被解析的次數



點選(此處)摺疊或開啟

  1. --改變觸發器的定義,使用儲存過程封裝具體執行的程式碼
  2. CREATE OR REPLACE PROCEDURE proc_tri(p_deptno IN NUMBER)
  3. AS
  4. BEGIN
  5.   INSERT INTO t2(deptno, cnt) VALUES(p_deptno, 1);
  6. END;
  7. /

  9. CREATE OR REPLACE TRIGGER tri_t1_insert
  10. AFTER INSERT ON t1
  11. FOR EACH ROW
  12. BEGIN
  13.   proc_tri(:new.deptno);
  14. END tri_t1_insert;
  15. /


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17013648/viewspace-1071345/，如需轉載，請註明出處，否則將追究法律責任。

相關文章