有一位兄弟，问了一问题，
用触发器实现一个功能，如果插入的字段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错误，感兴趣的朋友可以试一试，要是有结果，可以贴出来，分享一下。
