Re: S.O.S. Oracle trigger in PL/SQL
Date: 1998/02/18
Message-ID: <6cgau0$mik$1_at_usenet11.supernews.com>
Novita,
You can't perform a select from the table firing the trigger.
Oracle doesn't like that. Try your update
( update B table set flight suffix=:new.flight suffix where
flight id=:new.flight id;)
without the initial select. You have the value of :new.flight suffix
already. You also have the old value in
:old.flight suffix. Remember, you can't do a commit from a trigger.
Hope this helps.
Rick.
Novita Leung wrote in message <34EB8D80.9C285822_at_netvigator.com>...
Hi,
[Quoted] I fall into a very serious problem in applying Oracle trigger on
'update' in PL/SQL. There is 2 tables involved in the
trigger scripts. Whenever there is an 'update' in table A, old values of the corresponding fields must be stored in table
B. Here is problem comes!
create trigger scott.changes
BEFORE UPDATE on scott.A table for each row DECLARE flight suffix number(10); BEGIN select flight suffix into flight suffix from A table where flight id=:new.flight id; update B table set flight suffix=:new.flight suffix where flight id=:new.flight id; end;
However, when an Oracle update statement is executed on A table, error prompts as following: 'ORA-04091: table scott.a table is mutating, trigger/function may not see it'
scenario 1) when 'update' applied in A table instead of selecting fields, errors won't prompt but update don't work
neither. As a result a desired field didn't get updated with new values after trigger executed.
scenario 2) trigger BEFORE / AFTER update don't make a difference
PLEASE HELP!
thanks,
Novita
--Received on Wed Feb 18 1998 - 00:00:00 CET