CODEWITHSUNDEEP
oracle-databasetriggersmove
raph
raph

Reputation: 1

Oracle DB, move row on update to another table

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

Upvotes: 0

Views: 148

Answers (3)

Elshan Akhundov
Elshan Akhundov

Reputation: 1

I had faced with same issue, better way it to avoid using triggers for such cases and change architecture of application. May be doing this kind of stuff on application side.

Here are some important items to remember about triggers.

On insert triggers have no :OLD values.

On delete triggers have no :NEW values.

Triggers do not commit transactions. If a transaction is rolled back, the data changed by the trigger is also rolled back.

Commits, rollbacks and save points are not allowed in the trigger body. A commit/rollback affects the entire transaction, it is all or none.

Unhandled exceptions in the trigger will cause a rollback of the entire transaction, not just the trigger.

If more than one trigger is defined on an event, the order in which they fire is not defined. If the triggers must fire in order, you must create one trigger that executes all the actions in the required order.

A trigger can cause other events to execute triggers. A trigger can not change a table that it has read from. This is the mutating table error issue.

Upvotes: 0

raph
raph

Reputation: 1

exactly that ! mutating

I Have found a way to get it works :

create or replace TRIGGER TRIGGER1 After UPDATE OF AUTOMATICCOUNT ON SCAN4000

BEGIN insert into SCAN4000_SAP (datamatrixcode, automaticcount) select scan4000.datamatrixcode, scan4000.automaticcount from scan4000 where automaticcount is not null; Delete from SCAN4000 where scan4000.automaticcount is not null; END;

A colleague just told me that this could lead to DEADLOCKS, what do you think ?

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143003

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.

Upvotes: 0

Related Questions