一个触发器需求的案例

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 一个触发器需求的案例

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

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

一个触发器需求的案例

      一个触发器需求的案例  一个触发器需求的案例      

一个触发器需求的案例
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 一个触发器需求的案例


 上一篇
上周上线碰见的ORA-00054错误回放 上周上线碰见的ORA-00054错误回放
上周应用上线,有一个数据库脚本,包含改字段长度等操作,执行过程中,现象就是有些改字段成功了,有些执行出错,报了ORA-00054的错误。了解一下原理,就能对这个错误,有比较深入的理解了。 首先,我们模拟下报错过程,创建测试表,session
下一篇 
MySQL远程访问权限的设置 MySQL远程访问权限的设置
今儿有位同事提出,一套MySQL 5.6的环境,从数据库服务器本地登录,一切正常,可是若从远程服务器访问,就会报错, ERROR 1045 (28000): Access denied for user ‘bisal’@’x.x.x.x