oracle before after 触发器,触发器 after before 之间的区别

感谢Frank Zhou的指点，asktom上面有很好的说明：

http://asktom.oracle.com/pls/ask ... ON_ID:1357244410566

a before trigger can prevent the work from ever taking place -- an AFTER trigger

will have allowed the work to happen.  So, if you were using a statement trigger to do

additional privilege checking -- it would be infinitely more performant in a BEFORE

trigger, thereby preventing the work from ever happening (less wasted cycles processing a

statement that will be rolled back which itself is expensive).

A before row level trigger can modify the :new values, an after row trigger cannot.

a before trigger has limitations too -- let's compare a before to an after ROW trigger:

a) before for each row -- can modify the values HOWEVER, a before for each row trigger cannot see

what value will actually be placed into the table (hence, never use a before for each row trigger

to verify values!!! it may not be seeing the final value)

b) after for each row -- cannot modify the values HOWEVER, an after for each row trigger sees the

value of the column(s) that will actually be inserted into the table.

(ONLY the after for each row can look at a "stable" value in the :new record.  So, if you

doing data validation , you should do that in an AFTER trigger because the BEFORE triggers may change the

value on you (and since BEFORE triggers fire in SOME RANDOM order -- you cannot be

assured that your BEFORE trigger fires before or after some other BEFORE trigger.

use BEFORE FOR EACH row when you need to WRITE to the :new record

use AFTER FOR EACH row triggers when you want to VALIDATE the final values

in the :new record

)

Additionally -- you'll find an AFTER trigger to cause less redo to be generated.

Note:  ( Oracle documents )

AFTER row triggers are slightly more efficient than BEFORE row triggers. With BEFORE row triggers, affected data blocks must be read (logical read, not physical read) once for the trigger and then again for the triggering statement.

Alternatively, with AFTER row triggers, the data blocks must be read only once for both the triggering statement and the trigger.

大意：

Before 触发器可以防止修改发生, after触发器则为事后校验; 如果是做权限检查, 用BEFORE触发器更为高效，因为可以减少昂贵的回滚处理。

BEFORE 行触发器可以修改:NEW的值，AFTER行触发器不可以。BEFORE行触发器看到的不是最终的值，而且如果有多个BEFORE行触发器，它们的触发是无序的、随机的，在一个触发器中看到的数据可能随后被另一个改动，所以不能用于数据校验，应该改用AFTER行触发器。

如果你需要修改:NEW的值就用BEFORE行触发器，如果你需要校验:NEW的值就用AFTER行触发器。此外AFTER触发器会产生较少的REDO。

Oracle文档中的注解：

AFTER行触发器比BEFORE行触发器稍微高效。使用BEFORE触发器，涉及到的数据块必须为触发器读入一次然后又为触发的语句读入一次(逻辑读，非物理读)。而AFTER行触发器则只需读一次。