java写触发器例子_一个触发器需求的案例

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

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