How to Insert affected rows in a new table

Question

Following is a dummy table on which i am performing some updations. Here, i am only manipulating the "NAME" column. What i need is to insert the affected row, (say) i change the second for for "ID"-2, then i need that only this row should get inserted in a new table with new / updated value. I have tried doing so via a trigger but it vomits an error regarding table mutation.

   ID    NAME
================ 
    1    Vaz
    2    Dan
    3    Steve

The table i want the data to get inserted has the same structure as the above mentioned table and same columns with same datatype.

Please suggest if this could be done in any other way or I am writing wrong code for the trigger. Here is the code i wrote for the purpose :

CREATE OR REPLACE TRIGGER HR.ins_temp
after UPDATE OF name ON HR.TEMP2 FOR EACH ROW
BEGIN
INSERT INTO temp3
(SELECT NAME
FROM temp2
WHERE (:new.NAME<>:old.NAME));
END;

temp2 is the manipulation tabel and temp3 is the new one.

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2017-02-22T07:11:50

No need for a SELECT. And you probably also want to put the ID value into the table temp3

CREATE OR REPLACE TRIGGER HR.ins_temp
after UPDATE OF name ON HR.TEMP2 FOR EACH ROW
BEGIN
    INSERT INTO temp3 (id, name)
    values (:new.id, :new.name);
END;
/

And as the trigger is defined as update of name you don't really need to check if the name changed, but if you want, you can do:

CREATE OR REPLACE TRIGGER HR.ins_temp
after UPDATE OF name ON HR.TEMP2 FOR EACH ROW
BEGIN
  if (   (:new.name <> :old.name)
      OR (:new.name is null and :old.name is not null)
      OR (:new.name is not null and :old.name is null)) 
 then
    INSERT INTO temp3 (id, name)
    values (:new.id, :new.name);
  end if;
END;
/

How to Insert affected rows in a new table

1 Answers