Ice Ice - 4 months ago 17
SQL Question

How to create a trigger which, update a row in the same table after insert?

For example, I have a table t_1 and I insert only

id
value, but trigger should after insert, update value
id_2
and set
id_2
to
id
:

create table t_1(
id number(10),
id_1 number(10)
);


I create trigger:

create or replace trigger id_to_id_2
after insert
on t_1
for each row
begin
update t_1 set id_2=:new.id where id = new.id;
end;

/


But when I try insert, I get the error:

DB constraint error: ORA-04091: table t_1is mutating, trigger/function may
not see it\nORA-06512: at


I don't understand the error, could you explain me what am I doing wrong?

Answer

You need a before insert trigger like this:

create or replace trigger id_to_id_2
before insert
on t_1
for each row
begin
  :new.id_2=:new.id;
end;