关于触发器在行级和语句级的执行顺序问题

原创于2008年01月30日，2009年10月18日迁移至此。

原来总是对触发器的几种写法和执行先后顺序感到困惑，找了个时间把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,
TestNameVARCHAR2(20) NOT NULL,
CreateDTDATE,
UpdateDTDATE
);
ALTER TABLE test ADD CONSTRAINT TestPrimaryKey PRIMARY KEY (TestID);
CREATE TABLE TriggerLog
(
SeqID NUMBER(20,0),
TriggerNameVARCHAR2(50),
TableName VARCHAR2(30),
FieldName VARCHAR2(30),
FieldValue VARCHAR2(30),
OperateOrder VARCHAR2(30),
OperateTypeVARCHAR2(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触发器，每条语句仅执行一次 ITPUB个人空间�T's6q @u-W2n#e ITPUB个人空间"Qn{-O�GW"`@ ITPUB个人空间)T8t G5t^C-F+] "X7N(N:/B%A%y8cs6517 (Gy%b6JK-v9B8_jJ6517