融会贯通学习trigger(r2笔记第4天)

很多的东西在工作中用到的时候才能理解深刻，有些东西停留在理论层面而不去实践，就不会真正理解。 昨天写了一个很简单的trigger，但是中间也费了一些周折。 系统中碰到一个很严重的问题，一个数据处理引擎是基于表驱动设计的，里面的一个表中已经pending了很多的事务信息，对系统造成了严重的影响，为了第一时间排查这个问题，同事为了避免对目前的事务处理的进一步影响。讨论最后决定，我们需要修改一个字段的值，把它改为一个不被系统设别的值，这样新的事务信息就不会对目前的问题排查工作所影响。 这个时候需要dba要做的就是每一个新增的事务都需要修改那个字段的值，比如我们设定字段为source_comp_id，这个字段的值为2003的时候就被系统设别，能够进一步处理，如果设定为203，系统就不舍别，就不会处理，我们现在要做的就是把每一个新增的事务的source_comp_id值都去掉一位。 这个用trigger实现还是很自然的，但是因为这个表是生产的一个很大的分区表，自己分析和考虑的时候就走了一些弯路。 这个过程中也对trigger有了进一步的学习。发现自己很多以前的东西都忘记了。 首先从性能的角度考虑，能不能从字段的级别进行触发，类似下面的样子。 CREATE TRIGGER pub_source_code_chg_trg before insert of source_comp_id ON test_number for each row begin ..... end / 马上得到了错误，所以基于列的修改，insert还是不支持的。还有delete也是同样的问题，update就可以。 before INSERT of SOURCE_COMP_ID * ERROR at line 2: ORA-04073: column list not valid for this trigger type 好了继续学习。 接着我写了如下的trigger: create or replace trigger pub_source_code_chg_trg after insert on trb1_pub_log for each row begin if (:new.source_comp_id =2003) then update pub_log set source_comp_id = 203,buffer_id=-1 where source_comp_id=:new.SOURCE_COMP_ID and PUB_TRX_ID=:new.PUB_TRX_ID and BUFFER_ID=:new.BUFFER_ID; commit; end if; end; / 看似很简单，已经完成了。 做了一个简单的Insert操作。就报了如下的错误。看来创建好了只是开始，还不能用。 insert into app_tmp.pub_log select *from test_pub_log where source_comp_id=2003 and rownum<2 * ERROR at line 1: ORA-04091: table APP_TMP.PUB_LOG is mutating, trigger/function may not see it ORA-06512: at "APP_TMP.PUB_SOURCE_CODE_CHG_TRG", line 3 ORA-04088: error during execution of trigger 'APP_TMP.PUB_SOURCE_CODE_CHG_TRG' 最后发现是同一个表insert的同时做update会有问题，果断加了自治事务的部分。 create or replace trigger pub_source_code_chg_trg after insert on trb1_pub_log for each row declare pragma autonomous_transaction; begin if (:new.source_comp_id =2003) then update pub_log set source_comp_id = 203,buffer_id=-1 where source_comp_id=:new.SOURCE_COMP_ID and PUB_TRX_ID=:new.PUB_TRX_ID and BUFFER_ID=:new.BUFFER_ID; commit; end if; end; / 又做了简单的Insert，看似就没有问题了，但是数据有问题。 -->做insert操作前。有9条记录都是2003. SQL> select source_comp_id from trb1_pub_log; SOURCE_COMP_ID -------------- 2003 2003 2003 2003 2003 2003 2003 2003 2003 9 rows selected. insert into app_tmp.pub_log select *from test_pub_log where source_comp_id=2003 and rownum<3 2 rows created. 没有提交，结果source_comp_id就自动改变了。 SQL> select source_comp_id from trb1_pub_log; SOURCE_COMP_ID -------------- 203 203 203 2003 2003 2003 2003 2003 2003 2003 2003 最后发现还是这个自治事务的问题，在insert的过程中，所做的Update修改的数据不是insert的数据。 这个也是自治事务不是很常用的原因。 还是先来实现目标才考虑其他的因素吧。 下面的这个triggr就实现了新增事务的字段值修改。 CREATE OR REPLACE TRIGGER trb_pub_source_code_chg_trg before INSERT ON trb1_pub_log for each row begin if (:new.source_comp_id =2003) then :new.source_comp_id := 203; :new.buffer_id:=-1 ; end if; end; / 但是还有一个问题。报了ora-14402的问题，这个时候可以启用row movement就可以了。 insert into app_tmp.pub_log select *from test where source_comp_id=2003 and rownum<3 * ERROR at line 1: ORA-14402: updating partition key column would cause a partition change ORA-06512: at "APP_TMP.PUB_SOURCE_CODE_CHG_TRG", line 5 ORA-04088: error during execution of trigger 'APP_TMP.PUB_SOURCE_CODE_CHG_TRG' 做了好几轮测试，考虑了很多的因素，都没有发现问题。 所以大道至简，这也给自己好好上了一课。