Re: S.O.S. Oracle trigger in PL/SQL

From: Rick Slingwine <_at_mitec.net>
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

Original text of this message