Mutuation Trigger

[Thanh553]

I am trying to update the changed date of a table. For instance, I have a table with 4 columns: column1, column2, column3, changed_dt.
If any of the first 3 columns get update, I want the changed date to update.

CREATE TRIGGER MyTestTrigger
    AFTER UPDATE of "column1", "column2", "column3"
    ON Mytable REFERENCING OLD AS OLD NEW AS NEW
    FOR EACH ROW
begin

IF  UPDATING THEN
                UPDATE Mytable 
	SET CHANGED_DT = SYSDATE;
END IF;

END;
/

However, I am getting the mutuating error. Why?

[debasisdas]

Please find more about mutating trigger here and here .

[subashsavji]

one of the best example to avoid mutation problem

[code=oracle]

Create table CUG

drop table CUG cascade constraints;
create table CUG (
id_cug number(12) not null primary key,
id_B number(12) not null,
type number(1),
foreign key (id_B) references CUG (id_cug)
on delete cascade);

--Next we create a temporary table to avoid the "Mutating Table Problem".

drop table CUGTMP;
create global temporary table CUGTMP (
id_B number(12),
type number(1))
on commit delete rows;

--The following trigger checks new rows (Inserts) in CUG

create or replace trigger bi_r
before insert on CUG
for each row
declare
l_type CUG.type%type;
begin
if (:new.type in (3,4)) then
select type into l_type from CUG
where id_cug = :new.id_B;
end if;
if (l_type != 2) then
raise_applicati on_error(-20002,
'C and D CUGs must have a leading B');
end if;
end;
/

--The following Trigger saves the new values for id_B in the temporary table.

create or replace trigger au_r
after update of id_B on CUG
for each row
begin
insert into CUGTMP (id_B,type)
values (:new.id_B,:new .type);
end;
/

--The following Trigger finally checks, that C and D CUGs belong to a B CUG.

create or replace trigger au_s
after update of id_B on CUG
declare
l_id_B number(12);
l_typeCD number(1);
l_typeB number(1);
cursor cur_cugtmp is
select id_B,type
from CUGTMP;
begin
open cur_cugtmp;
loop
fetch cur_cugtmp into l_id_B,l_typeCD ;
exit when cur_cugtmp%notf ound;
select type into l_typeB from CUG
where id_cug = l_id_B;
if (l_typeB != 2) then
raise_applicati on_error(-20002,
'C and D CUGs must have a leading B');
end if;
end loop;
close cur_cugtmp;
end;
/
[/code]

Test insert and update