Copy a single record to a replica table before update using trigger (Oracle 12c)

oracletrigger

I want to insert a complete record (before updating original record) to a replica table using trigger. Following are the sample tables:

create table customer
(
     cID number,
     cName varchar2(30)
     -- all other attributes will be defined here
);

create table replica
as (select * from customer where 1 = 2);

and trigger:

create or replace trigger update_Trigger
before update on customer
for each row
begin
    if updating then
        insert into replica
            select * from customer where cID = :old.cID;
          -- also done with :new.cID; but not effect...
    end if;
end;

Table is not empty, I have some records in it. But when I run update statement on customer table, I get error:

SQL> UPDATE CUSTOMER
  2  SET CNAME = 'C' WHERE CID = 102;


UPDATE CUSTOMER
       *
ERROR at line 1:
ORA-04091: table DBADMIN.CUSTOMER is mutating, trigger/function may no
ORA-06512: at "DBADMIN.UPDATE_TRIGGER", line 3
ORA-04088: error during execution of trigger 'DBADMIN.UPDATE_TRIGGER'

What is wrong with this? What am I missing? I know this can be done by using :new.attribute and :old.attribute but there many attributes in the original table, I am working on. Is there any short method for getting and inserting the whole row?

Best Answer

You can use this format for copying your original values over:

CREATE OR REPLACE TRIGGER update_Trigger
BEFORE UPDATE ON customer
FOR EACH ROW
BEGIN
    IF UPDATING THEN
        INSERT INTO  replica VALUES(:OLD.cID, :OLD.cName, ... );
    END IF;
END;
/

You will need to specify each field you want inserted into replica

This will be slow and cause a lot of network/database traffic if you are updating 1,000s of rows at a time.

Related Question