一个触发器需求的案例

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

用触发器实现一个功能，如果插入的字段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的个人杂货铺，您的支持是对我最大的鼓励！共同学习，共同进步:)