關於觸發器在行級和語句級的執行順序問題

原來總是對觸發器的幾種寫法和執行先後順序感到困惑，找了個時間把Oracle的官方文件看了一下，然後做了幾個例子，終於有點明白了。:
Types of Triggers
觸發器型別
Row Triggers and Statement Triggers
行級觸發器和語句級觸發器
BEFORE and AFTER Triggers
BEFORE和AFTER觸發器
INSTEAD OF Triggers
INSTEAD OF觸發器
Triggers on System Events and User Events
系統事件和使用者事件觸發器
--後面兩種暫時不討論
Trigger Type Combinations
組合觸發器型別
Using the options listed previously, you can create four types of row and statement triggers:
根據前面所列的選項，我們能夠建立四種型別的行級和語句級觸發器
BEFORE statement trigger
BEFORE 語句級觸發器
Before executing the triggering statement, the trigger action is run.
執行觸發SQL語句之前，就會啟用觸發器動作。
BEFORE row trigger
BEFORE 行級觸發器
Before modifying each row affected by the triggering statement and before checking appropriate integrity constraints, the trigger action is run, if the trigger restriction was not violated.
在修改由觸發SQL語句影響的每一行記錄之前或者在檢查完整性約束之前，將會執行觸發動作。
AFTER row trigger
AFTER 行級觸發器
After modifying each row affected by the triggering statement and possibly applying appropriate integrity constraints, the trigger action is run for the current row provided the trigger restriction was not violated. Unlike BEFORE row triggers, AFTER row triggers lock rows.
在修改由觸發SQL語句影響的每一行記錄之後或者在滿足完整性約束之後，將會執行觸發動作。和BEFORE行級觸發器不同，AFTER行級觸發器將會鎖定記錄。
AFTER statement trigger
AFTER 語句級觸發器
After executing the triggering statement and applying any deferred integrity constraints, the trigger action is run.
在執行完畢觸發SQL語句之後和確保不違反完整性約束的情況下，將會執行該觸發動作。
1. 建立一張資料表和一張記錄觸發動作的表，再建立一個序列用來記錄各個觸發器觸發動作的先後順序。
CREATE TABLE test
(
  TestID    INTEGER NOT NULL,
  TestName  VARCHAR2(20) NOT NULL,
  CreateDT  DATE,
  UpdateDT  DATE
);
ALTER TABLE test ADD CONSTRAINT TestPrimaryKey PRIMARY KEY (TestID);
CREATE TABLE TriggerLog
(
  SeqID        NUMBER(20,0),
  TriggerName  VARCHAR2(50),
  TableName    VARCHAR2(30),
  FieldName    VARCHAR2(30),
  FieldValue   VARCHAR2(30),
  OperateOrder VARCHAR2(30),
  OperateType  VARCHAR2(30),
  OperateDT    DATE
);
-- Create sequence
CREATE SEQUENCE SeqTriggerLog
MINVALUE 1
MAXVALUE 100000
START WITH 1
INCREMENT BY 1;
2. 建立4個觸發器，分別為前置後置行級語句級的組合
CREATE OR REPLACE TRIGGER TrgBefInsStateOnTest
  BEFORE INSERT ON test  
BEGIN
  INSERT INTO TriggerLog VALUES
    (SeqTriggerLog.NextVal,'TrgBefInsStateOnTest','test','TestName',' ','BEFORE','INSERT',SYSDATE);
END TrgBefInsStateOnTest;
--在Before行級語句上可以對受影響的記錄進行預處理
CREATE OR REPLACE TRIGGER TrgBefInsRowOnTest
  BEFORE INSERT ON test  
  FOR EACH ROW BEGIN
    :new.CreateDT:=SYSDATE;
    INSERT INTO TriggerLog VALUES
      (SeqTriggerLog.NextVal,'TrgBefInsRowOnTest','test','TestName',:new.TestName,'BEFORE','INSERT',SYSDATE);
  END TrgBefInsRowOnTest;
--在After行級語句上可以進行相關完整性資料維護，當然對UPDATE更明顯一些
CREATE OR REPLACE TRIGGER TrgAftInsRowOnTest
  AFTER INSERT ON test  
  FOR EACH ROW BEGIN
    INSERT INTO TriggerLog VALUES
      (SeqTriggerLog.NextVal,'TrgAftInsRowOnTest','test','TestName',:new.TestName,'AFTER','INSERT',SYSDATE);
  END TrgAftInsRowOnTest;
CREATE OR REPLACE TRIGGER TrgAftInsStateOnTest
  AFTER INSERT ON test  
BEGIN
  INSERT INTO TriggerLog VALUES
    (SeqTriggerLog.NextVal,'TrgAftInsStateOnTest','test','TestName',' ','AFTER','INSERT',SYSDATE);
END TrgAftInsStateOnTest;
3. 首先一次性插入多條記錄，然後分別單獨插入兩條記錄，看看其執行的先後
INSERT INTO test(testid,testname) SELECT column_id,column_name FROM user_tab_columns WHERE table_name='TEST';  
COMMIT;
INSERT INTO test(testid,testname) VALUES(5,'AAA');
INSERT INTO test(testid,testname) VALUES(6,'BBB');
COMMIT;
SELECT * FROM test;
SELECT * FROM TriggerLog;

最終執行觸發器的先後順序如下
1. 首先執行Before Insert State觸發器，每條語句僅執行一次
2. 其次執行Before Insert Row觸發器，為SQL語句影響的記錄數的多少
3. 再次執行After Insert Row觸發器，為SQL語句影響的記錄數的多少
4. 最後執行After Insert State觸發器，每條語句僅執行一次

[ 本帖最後由 bq_wang 於 2008-1-30 00:34 編輯 ]