You Asked

hello Tom,
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 (4 first records are 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 question: is it possible to fix in trigger? It must work with insert/update and merge and control must be in trigger.

regards,
Roman

and Connor said...

You need to send us a test case ... I'm not seeing any dramas, eg


SQL> drop table t purge;

Table dropped.

SQL> create table T ( x int, y int , z int);

Table created.

SQL>
SQL>
SQL> create or replace
  2  trigger TRIG for insert or update or delete on T compound trigger
  3
  4    type each_row is record ( rid rowid,
  5                              action varchar2(1)
  6                              );
  7    type   row_list is table of each_row index by pls_integer;
  8
  9    g      row_list;
 10    l_action varchar2(1) :=
 11      case
 12         when updating then 'U'
 13         when deleting then 'D'
 14         when inserting then 'I'
 15         else 'O'
 16       end;
 17     z_val int;
 18
 19  before statement is
 20  begin
 21    g.delete;
 22  end before statement;
 23
 24  after each row is
 25  begin
 26      g(g.count+1).rid     :=  :new.rowid;
 27      g(g.count).action    :=  l_action;
 28  end after each row;
 29
 30  after statement is
 31  begin
 32    for i in 1 .. g.count loop
 33       dbms_output.put_line(g(i).rid);
 34       dbms_output.put_line(g(i).action);
 35
 36       if g(i).action in ('I','U') then
 37           select z into z_val from t
 38           where rowid = g(i).rid;
 39           dbms_output.put_line('Did a query, z='||z_val);
 40       end if;
 41    end loop;
 42    g.delete;
 43  end after statement;
 44
 45  end;
 46  /

Trigger created.

SQL> sho err
No errors.
SQL>
SQL> truncate table t;

Table truncated.

SQL>
SQL> set serverout on
SQL> insert into T values (0,0,0);
AAAcPpAAGAAB0AkAAA
I
Did a query, z=0

1 row created.

SQL> insert into T
  2  select rownum, rownum , rownum*1000 from dual
  3  connect by level <= 10;
AAAcPpAAGAAB0AkAAB
I
Did a query, z=1000
AAAcPpAAGAAB0AkAAC
I
Did a query, z=2000
AAAcPpAAGAAB0AkAAD
I
Did a query, z=3000
AAAcPpAAGAAB0AkAAE
I
Did a query, z=4000
AAAcPpAAGAAB0AkAAF
I
Did a query, z=5000
AAAcPpAAGAAB0AkAAG
I
Did a query, z=6000
AAAcPpAAGAAB0AkAAH
I
Did a query, z=7000
AAAcPpAAGAAB0AkAAI
I
Did a query, z=8000
AAAcPpAAGAAB0AkAAJ
I
Did a query, z=9000
AAAcPpAAGAAB0AkAAK
I
Did a query, z=10000

10 rows created.

SQL>
SQL> delete from t where x = 1;
AAAcPpAAGAAB0AkAAB
D

1 row deleted.

SQL>
SQL> merge into T
  2  using (
  3    select 1 x1, 10 y1, 100 z1 from dual
  4    union all
  5    select 2 x1, 20 y1, 200 z1 from dual
  6    ) m
  7  on ( t.x = m.x1 )
  8  when matched then update set t.y = m.y1
  9  when not matched then insert values (m.x1,m.y1,m.z1 );
AAAcPpAAGAAB0AkAAL
I
Did a query, z=100
AAAcPpAAGAAB0AkAAC
U
Did a query, z=2000

2 rows merged.

SQL>
SQL>

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

example is wrong, hereby one

A reader, February 15, 2016 - 7:30 am UTC

CREATE TABLE Employees (
Empno NUMBER,
Ename VARCHAR2(10),
Salary NUMBER(7,2),
Department_ID NUMBER(2));
/

Insert into EMPLOYEES
(empno, ename, salary, department_id)
Values
(1, 'john', 1000, 1);
Insert into EMPLOYEES
(empno, ename, salary, department_id)
Values
(2, 'peter', 2000, 1);
Insert into EMPLOYEES
(empno, ename, salary, department_id)
Values
(3, 'olga', 1500, 2);
COMMIT;

CREATE OR REPLACE TRIGGER Check_Employee_Salary_Raise
FOR UPDATE OF Salary ON Employees
COMPOUND TRIGGER
Ten_Percent CONSTANT NUMBER := 0.1;
TYPE Salaries_t IS TABLE OF Employees.Salary%TYPE;
Avg_Salaries Salaries_t;
TYPE Department_IDs_t IS TABLE OF Employees.Department_ID%TYPE;
Department_IDs Department_IDs_t;

TYPE Department_Salaries_t IS TABLE OF Employees.Salary%TYPE
INDEX BY VARCHAR2(80);
Department_Avg_Salaries Department_Salaries_t;

BEFORE STATEMENT IS
BEGIN
SELECT AVG(e.Salary), NVL(e.Department_ID, -1)
BULK COLLECT INTO Avg_Salaries, Department_IDs
FROM Employees e
GROUP BY e.Department_ID;
FOR j IN 1..Department_IDs.COUNT() LOOP
Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j);
END LOOP;
END BEFORE STATEMENT;

AFTER EACH ROW IS
BEGIN
IF :NEW.Salary - :Old.Salary >
Ten_Percent*Department_Avg_Salaries(:NEW.Department_ID)
THEN
Raise_Application_Error(-20000, 'Raise too big');
END IF;
END AFTER EACH ROW;
END Check_Employee_Salary_Raise;
/

merge into Employees e
using (select 4 empno, 'jan' ename, 3000 salary from dual) t
on (e.empno = t.empno)
when matched then update set e.salary = t.salary
when not matched then insert (e.empno,e.ename,e.salary) values (t.empno,t.ename,t.salary)

February 16, 2016 - 1:12 am UTC

Replicated with this smaller example

SQL> drop table t purge;

Table dropped.

SQL> create table t ( x ,y ) as select 1,1 from dual;

Table created.

SQL> alter session set Plsql_Warnings = 'Error:All';

Session altered.

SQL> create or replace trigger trg
  2  for update or insert on t
  3  compound trigger
  4    type nlist is table of number;
  5
  6    x1 nlist;
  7    y1 nlist;
  8
  9  before statement is
 10  begin
 11      select x,y
 12      bulk collect into x1,y1
 13      from t;
 14  end before statement;
 15
 16  end;
 17  /

Trigger created.

SQL> update t set x = x + 1;

1 row updated.

SQL> insert into t values (2,2);

1 row created.

SQL> insert into t
  2  select rownum, rownum
  3  from dual connect by level <= 5;

5 rows created.

SQL> merge into t
  2  using (select 2 new_x,2 new_y from dual) m
  3  on (t.x = m.new_x)
  4  when matched then update set t.y = m.new_y
  5  when not matched then insert (x,y) values (m.new_x,m.new_y) ;
merge into t
           *
ERROR at line 1:
ORA-04091: table SCOTT.T is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TRG", line 9
ORA-04088: error during execution of trigger 'SCOTT.TRG'

You probably want to log a support call for this one.

Cross-posting :(

John Stegeman, February 15, 2016 - 12:49 pm UTC

Roman,

Cross-posting without telling people you're doing so is usually considered somewhat rude, at best.

https://community.oracle.com/thread/3897669 In your post over there, you have been given advice that triggers are not an appropriate way to do this. Fishing for a different answer - well, I don't know what you expect... I did that when I was 5 years old - mum says no, try asking dad.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

example is wrong, hereby one

Cross-posting :(

More to Explore

PL/SQL demos

PL/SQL docs

solution for mutating table does not work with MERGE (compoud trigger)

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

example is wrong, hereby one

Cross-posting :(

More to Explore

PL/SQL demos

PL/SQL docs