2

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?

2

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.