hello guys,
I get mutating table if i use MERGE instead of update and insert. I made compoud trigger (oracle 11.2) to avoid mutating table: in before stament i make a plsql table (here i use the same table) with all i need and in after each row i do cheks and raise if it needed.
It works fine but not with merge. Cause - during the merge BEFORE statment fires always twice - for inserting and for updating... and that is the problem. See output of the trigger below (red is the problem)
0001===== rf_rep_object_comp_iu: before statement
====>> 0001===== rf_rep_object_comp_iu: before statement INSERTING
0001===== rf_rep_object_comp_iu: before statement
====>> 0001===== rf_rep_object_comp_iu: before statement UPDATING
0002===== rep_object_comp_iu: before each row
====>> 0002===== rep_object_comp_iu: before each row UPDATING
0003===== rf_rep_object_comp_iu: after each row
====>> 0003===== rf_rep_object_comp_iu: after each row UPDATING
>>>>>>>>>> old.obj_id:8272>>>> new.obj_id:8272
>>>>>>>>>> old.ojs_id: 8>>>> new.ojs_id: 8
0004===== rf_rep_object_comp_iu: after statement
====>> 0004===== rf_rep_object_comp_iu: before statement INSERTING
0004===== rf_rep_object_comp_iu: after statement
====>> 0004===== rf_rep_object_comp_iu: before statement UPDATING
The same case a saw here but without solution.
http://www.progtown.com/topic514768-statement-trigger-merge-ora04088.html
The questin is: is it possible to fix in trigger? It must work with insert/update and merge and control must be in trigger.
regards,
Roman