oracle 触发器 timing point (before or alter) 手记

        最近刚刚用到了oracle的触发器，突然就联想起来以前刚接触oracle用，写pl/sql遇到过关于触发器执行的时间点（timing point)的问题。于是就到oracle documents查找了以下。终于知道了他们的不同点。

        以下是oracle doc的原文：

BEFORE & AFTER

BEFORE
Causes the database to fire the trigger before running the triggering event. For row triggers, the trigger fires before each affected row is changed.
Restrictions on BEFORE 
• You cannot specify a BEFORE trigger on a view unless it is an editioning view.

• In a BEFORE statement trigger, the trigger body cannot read :NEW or :OLD. (In a BEFORE row trigger, the trigger body can read and write the :OLD and :NEW fields.)

AFTER
Causes the database to fire the trigger after running the triggering event. For row triggers, the trigger fires after each affected row is changed.

Restrictions on AFTER 
• You cannot specify an AFTER trigger on a view unless it is an editioning view.

• In an AFTER statement trigger, the trigger body cannot read :NEW or :OLD. (In an AFTER row trigger, the trigger body can read but not write the :OLD and :NEW fields.)

Note:
When you create a materialized view log for a table, the database implicitly creates an AFTER row trigger on the table. This trigger inserts a row into the materialized view log whenever an INSERT, UPDATE, or DELETE statement modifies data in the master table. You cannot control the order in which multiple row triggers fire. Therefore, do not write triggers intended to affect the content of the materialized view.

       大多数人在博客中，都说了before 与 after在执行时间点上的区别，但是在使用的时候，却无法区别他们。其实关键点就是在doc中标红&加粗的地方了。

下面是关于实验过程：

1.创建触发器(aflter)

CREATE or replace TRIGGER ver_data
AFTER DELETE ON dept FOR EACH ROW
BEGIN
    :new.deptno:=60;
  IF DELETING THEN
    UPDATE emp
    SET deptno = :new.deptno
    WHERE deptno = :old.deptno;
  END IF;
END;
 11  /
CREATE or replace TRIGGER ver_date
                          *
ERROR at line 1:
ORA-04084: cannot change NEW values for this trigger type
 

2.查看错误

SQL> ! oerr ora 4084
04084, 00000, "cannot change NEW values for this trigger type"
// *Cause:  New trigger variables can only be changed in before row
//          insert or update triggers.
// *Action: Change the trigger type or remove the variable reference.

3.创建触发器（before）

CREATE or replace TRIGGER vdate
before UPDATE OF deptno ON dept FOR EACH ROW
BEGIN
    :new.deptno:=61;
  IF (UPDATING AND :old.deptno != :new.deptno) THEN --关联标识（保留结构）
    UPDATE emp
    SET deptno = :new.deptno
    WHERE deptno = :old.deptno;
  END IF;
END;
 11  /

Trigger created.

4.查看触发器结果：

SQL> update dept set deptno=50 where deptno=20;

1 row updated.

SQL> select * from dept;

    DEPTNO DNAME      LOC
---------- -------------- -------------
    10 ACCOUNTING      NEW YORK
    61 RESEARCH      DALLAS
    30 SALES      CHICAGO
    40 OPERATIONS      BOSTON