Oracle get id of updated record in trigger update

Question

I have the following trigger for update action.

CREATE OR REPLACE TRIGGER oferta_trigger
  BEFORE UPDATE ON oferty
  FOR EACH ROW
DECLARE
  id_oferta     number (10);
  id_komis      number (10);
  id_test_komis number (10);
  suma          decimal(10,2) :=0;

  CURSOR c1 IS
    SELECT cena_aktualna, id_test_komis
      FROM oferty
      WHERE status = 'A';
BEGIN
  id_oferta := :NEW.idk;
  dbms_output.put_line(id_oferta);

  SELECT komis_id INTO id_komis FROM oferty WHERE idk = id_oferta;

  FOR i in c1 LOOP
    IF i.id_test_komis = id_komis THEN
       suma := suma + i.cena_aktualna;
    END IF;
  END LOOP;

  UPDATE komisy SET wartosc_samochodow = suma WHERE idk = id_komis;
END;

During update operation.

UPDATE oferty SET status ='Z' WHERE idk =1;

I get the following error:

SQL Error: ORA-04091: table OFERTY is mutating, trigger/function may not see it.

How to solve the problem. I think this is problem of getting id.

Answer 1

Here's an example based on Scott's schema. I altered the DEPT table, adding a new column (SUM_SAL) which is supposed to contain sum of all salaries in that department.

First, the good, old mutating table way.

SQL> create or replace trigger trg_sumsal
  2    after update on emp
  3    for each row
  4  declare
  5    l_sum number;
  6  begin
  7    select sum(sal) into l_sum
  8      from emp
  9      where empno = :new.empno;
 10
 11    update dept set sum_sal = l_sum
 12      where deptno = :new.deptno;
 13  end;
 14  /

Trigger created.

SQL> update emp set sal = 5000 where ename = 'KING';
update emp set sal = 5000 where ename = 'KING'
       *
ERROR at line 1:
ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TRG_SUMSAL", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRG_SUMSAL'


SQL>

As we already knew, that won't work.

Now, the compound trigger:

SQL> create or replace trigger trg_sumsal
  2    for update or insert on emp
  3    compound trigger
  4
  5    l_deptno emp.deptno%type;
  6
  7    after each row is
  8    begin
  9      l_deptno := :new.deptno;
 10    end after each row;
 11
 12    after statement is
 13      l_sum number;
 14    begin
 15      select sum(sal) into l_Sum
 16        from emp
 17        where deptno = l_deptno;
 18
 19      update dept set sum_sal = l_sum
 20      where deptno = l_deptno;
 21    end after statement;
 22  end;
 23  /

Trigger created.

SQL> update emp set sal = 10000 where ename = 'KING';

1 row updated.

SQL> select * From dept;

    DEPTNO DNAME          LOC              SUM_SAL
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK           13750
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

Nice; that works!

[EDIT, after reading William's comment]

If several departments are affected within a single UPDATE statement, the above code won't work properly. Slightly adjusted, it looks like this & fixes that issue:

SQL> create or replace trigger trg_sumsal
  2    for update or insert on emp
  3    compound trigger
  4
  5    type t_tab is table of number;
  6    l_tab t_tab := t_tab();
  7
  8    after each row is
  9    begin
 10      l_tab.extend;
 11      l_tab(l_tab.last) := :new.deptno;
 12    end after each row;
 13
 14    after statement is
 15      l_sum number;
 16    begin
 17      for i in l_tab.first .. l_tab.last loop
 18        select sum(sal) into l_Sum
 19          from emp
 20          where deptno = l_tab(i);
 21
 22        update dept set sum_sal = l_sum
 23          where deptno = l_tab(i);
 24      end loop;
 25    end after statement;
 26  end;
 27  /

Trigger created.

Testing:

SQL> select * from dept;

    DEPTNO DNAME          LOC              SUM_SAL
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> update emp set sal = 10000 where ename in ('SMITH', 'KING');

2 rows updated.

SQL> select * from dept;

    DEPTNO DNAME          LOC              SUM_SAL
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK           13750
        20 RESEARCH       DALLAS             15975
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>