一个触发器需求的案例

有一位兄弟，问了一问题，

用触发器实现一个功能，如果插入的字段AGE为空，则将此字段AGE的值置为0。

以下是一版实现，

SQL> create table t (id number, age number); Table created. SQL> CREATE OR REPLACE TRIGGER TR1   AFTER INSERT ON T   FOR EACH ROW   WHEN (NEW.AGE='') BEGIN   UPDATE T SET AGE=0 WHERE ID = :NEW.ID; END; / Trigger created.

执行插入操作，但NULL值，并未改为0，

SQL> insert into t values(1, ''); 1 row created. SQL> select * from t;     ID      AGE ---------- ----------      1

我对触发器，了解非常有限，只能试着来，乍一看判断空，即NULL，是不能用“=”，需要使用IS NULL/IS NOT NULL，改了一下，执行报错，

SQL> CREATE OR REPLACE TRIGGER TR1   AFTER INSERT ON T   FOR EACH ROW   WHEN (NEW.AGE IS NULL) BEGIN   UPDATE T SET AGE=0 WHERE ID = :NEW.ID; END; / Trigger created. SQL> insert into t values(1, ''); insert into t values(1, '')             * ERROR at line 1: ORA-04091: table BISAL.T is mutating, trigger/function may not see it ORA-06512: at "BISAL.TR1", line 2 ORA-04088: error during execution of trigger 'BISAL.TR1' oerr ora 4091 04091, 00000, "table %s.%s is mutating, trigger/function may not see it" // *Cause: A trigger (or a user defined plsql function that is referenced in //         this statement) attempted to look at (or modify) a table that was //         in the middle of being modified by the statement which fired it. // *Action: Rewrite the trigger (or function) so it does not read that table.

《After Update Trigger Fails With ORA-04091 When Modifying a Column in the Same Table (文档 ID 156388.1)》指出，

A mutating table is a table that is currently being modified by an update, delete, or insert statement.  Oracle returns the ORA-04091 error if a row trigger  reads or modifies the mutating table.   For example, ifa trigger contains a select statement or an update statement referencing the table it is triggering off of.

翻译一下，mutating table是指一个当前正在被update，delete，insert语句修改的表，如果在一个行级别的trigger中读取或修改一个mutating table，则往往会遇到ORA-04091错误。例如，如果在trigger中使用了select或者update语句访问trigger所在的表，就像上面这个触发器。

解决方法，使用PLSQL存储需要更新行的ROWID，在触发器中使用这个值，即利用临时变量，保存行信息，

One way to handle this situation is to use a package PL/SQL table to store ROWIDs of updated records in a row trigger, and reprocess the updated records in a statement trigger. 

参考这篇文章，《SQL: Example Workaround for ORA-4091 Error (文档 ID 37861.1)》。

除此之外，自治事务是另一种方法，重新写触发器，插入数据后对刚插入这条无效，但对已有符合条件的数据有效，需求是能更新正insert是最好的，但是目前的逻辑就是insert一条null值，用触发器相当于收尾，更新所有已有的null记录，如下所示，

SQL> select * from t;     ID      AGE ---------- ----------      1 SQL> CREATE OR REPLACE TRIGGER TR1   AFTER INSERT ON T   FOR EACH ROW   DECLARE   t_rec NUMBER;   PRAGMA AUTONOMOUS_TRANSACTION; BEGIN   t_rec := 0;   CASE   when INSERTING then   UPDATE T SET AGE=t_rec WHERE age is null;   end case;   COMMIT; EXCEPTION when OTHERS THEN ROLLBACK; END; / Trigger created. SQL> insert into t values(2, ''); 1 row created. SQL> select * from t;     ID      AGE ---------- ----------      1        0      2

既然不是收尾，是需要让当前INSERT的记录，判断若是NULL，则更新值为0，是不是需要使用BEFORE INSERT，而不是AFTER INSERT，执行发现报错，

SQL> CREATE OR REPLACE TRIGGER TR1   BEFORE INSERT ON T   FOR EACH ROW BEGIN   value := 0;   IF (:NEW.AGE IS NULL) THEN     :NEW.AGE:=0;   END IF; END; / Warning: Trigger created with compilation errors. SQL> show error Errors for TRIGGER TR1: LINE/COL ERROR -------- ----------------------------------------------------------------- 2/3     PL/SQL: Statement ignored 2/3     PLS-00321: expression 'VALUE' is inappropriate as the left hand      side of an assignment statement

调整一下参数值，定义变量，替换常量0，

SQL> CREATE OR REPLACE TRIGGER TR1   BEFORE INSERT ON T   FOR EACH ROW   DECLARE value NUMBER; BEGIN   value := 0;   IF (:NEW.AGE IS NULL) THEN     :NEW.AGE:=VALUE;   END IF; END; / SQL> insert into t values (1, ''); 1 row created. SQL> select * from t;     ID      AGE ---------- ----------      1        0

实现了最初的需求了，总结一下，使用BEFORE INSERT，插入之前，判断NEW.AGE是否为空，若是则用变量value=0赋值，此时执行INSERT，就会用0值，而不是原始NULL，进行操作。

若使用AFTER INSERT，我认为可以实现，但要注意避免，ORA-04091错误，感兴趣的朋友可以试一试，要是有结果，可以贴出来，分享一下。