triggers - Oracle DB, move row on update to another table

Question

at the beginning was thinking it was something really easy to perform, but i'am stuck with the mutating error using trigger and I have no other idea on how to perform it the main goal of this action is to avoid that the update line is performed twice ... so as soon as some fields are updated i need a process that move the row to another table (same fields in that table)

I have tried trigger with the AFTER update .. but mutating error

I hope someone could help me

thank you in advance

Raph

Answer 1

It is difficult to debug code you can't see; why didn't you post a test case so that we'd see what you did and how Oracle responded?

---

Because, it works as expected - if I understood the question correctly. Here's how.

SQL> create table test (id number, name varchar2(20));

Table created.

SQL> create table test_2 (id number, name varchar2(20), datum date);

Table created.

SQL> create or replace trigger trg_ai_test
  2    after insert on test
  3    for each row
  4  begin
  5    insert into test_2 (id, name, datum)
  6      values (:new.id, :new.name, sysdate);
  7  end;
  8  /

Trigger created.

Testing:

SQL> insert into test (id, name) values (1, 'Littlefoot');

1 row created.

SQL> select * from test;

        ID NAME
---------- --------------------
         1 Littlefoot

SQL> select * From test_2;

        ID NAME                 DATUM
---------- -------------------- -------------------
         1 Littlefoot           07.01.2021 13:47:31

SQL>

---

I presume that your trigger selects from the table you created the trigger on (in my example, that would be select ... from test). You can't do that because of the mutating table error, but - you don't have to do it either as you can use :new and/or :old columns' values.