COMPOUND TRIGGER學習

wenhual43發表於2013-06-22
今天看Oracle® Database PL/SQL Language Reference 11g Release 2 (11.2),對Compound DML Triggers寫了一些學習的測試指令碼。
背景:如果在觸發器裡對觸發器所在表進行DML,會報ORA-04091表變異的錯誤。
測試如下:
  建表:create table ABC_TEST
(
  zonecode    VARCHAR2(14),
  disease_id1 NUMBER(4) not null,
  intime      DATE,
  casetype    VARCHAR2(1),
  casetype2   VARCHAR2(1),
  dead        NUMBER
)

普通的DML觸發器:CREATE OR REPLACE TRIGGER log_deletions_1
  AFTER insert ON abc_test
  FOR EACH ROW
DECLARE
  n INTEGER;
BEGIN
  SELECT COUNT(*) INTO n FROM abc_test; --這句是重點,報錯就是這句引起的
  DBMS_OUTPUT.PUT_LINE('There are now ' || n || ' employees.');
END;

插入記錄:
SQL> insert into ABC_TEST values('43012200',198,sysdate,1,1,2);
 
insert into ABC_TEST values('43012200',198,sysdate,1,1,2)
 
ORA-04091: table HR.ABC_TEST is mutating, trigger/function may not see it
ORA-06512: at "HR.LOG_DELETIONS_1", line 4
ORA-04088: error during execution of trigger 'HR.LOG_DELETIONS_1'

如果一定要在觸發器查詢自己,可以建成複合觸發器,如下:
CREATE OR REPLACE TRIGGER log_deletions
FOR insert  ON abc_test
COMPOUND TRIGGER  --這個是關鍵字哦
  n INTEGER;
  BEFORE STATEMENT IS   --插入前查一遍表,這個會在insert之前執行
  begin  
      SELECT COUNT(1) INTO n FROM abc_test;
      DBMS_OUTPUT.PUT_LINE('There are now ' || n || ' employees.');
  END BEFORE STATEMENT;

  AFTER STATEMENT IS  --插入後執行,這個會在insert之後執行
  BEGIN
    SELECT COUNT(1) INTO n FROM abc_test;
    DBMS_OUTPUT.PUT_LINE('There are now ' || n || ' employees.');
  END AFTER STATEMENT;

END;


SQL> insert into ABC_TEST values('43012200',198,sysdate,1,1,2);
 
There are now 0 employees.
There are now 1 employees.
 
1 row inserted
 
SQL> commit;
 
Commit complete

看到效果了吧。複合觸發器,能在觸發器之前,先對錶進行查詢,在插入後,可以馬上看到插入的行,神奇吧。

好了,今天就寫到這,學習到compound trriger 的特性。

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

相關文章