有一位兄弟，问了一问题，
用触发器实现一个功能，如果插入的字段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错误，感兴趣的朋友可以试一试，要是有结果，可以贴出来，分享一下。
如果您觉得此篇文章对您有帮助，欢迎关注微信公众号：bisal的个人杂货铺，您的支持是对我最大的鼓励！共同学习，共同进步:)
