trigger runtime problem

highhighs · #1 (**permalink**) 05-21-04, 13:46

im having trouble in running a trigger ive created, the errors are ( at runtime)

SQL> update leds set price = price + 1 where color = 'White';
update leds set price = price + 1 where color = 'White'
*
ERROR at line 1:
ORA-04091: table SYSTEM.LEDS is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.LEDPRICECHANGE", line 5
ORA-06512: at "SYSTEM.LEDPRICECHANGE", line 10
ORA-04088: error during execution of trigger 'SYSTEM.LEDPRICECHANGE'

the trigger code is :
[Code:]
CREATE OR REPLACE TRIGGER LedPriceChange
AFTER UPDATE OF price ON Leds
FOR EACH ROW
WHEN (new.price > old.price)

DECLARE
diff INTEGER;
color Leds.color%TYPE;
CURSOR colors IS
select DISTINCT Leds.color
from Leds
where color = :new.color;
rt Leds.color%TYPE;
BEGIN
open colors;
diff := (:new.price -

ld.price)*100/(

ld.price); --get the percentage diff

while(colors%FOUND) loop
FETCH colors INTO rt;
incr_Back_L_price(diff,rt);
incr_Front_L_price(diff,rt);
end loop;
close colors;
END;
.
run;
[/CODE]

what does this error mean? im a newbi at this ( on oracle 10g).

thank you

gbrabham · #2 (**permalink**) 05-21-04, 13:49

A trigger on a specific table cannot reference that table in the trigger body ...
ie,

create trigger ...
on led

declare

select ... from led....

HTH
Gregg