Re: a simple Trigger question

If you look at the cause for the error in the Oracle errors manual, it will be something like this...

Cause: A trigger or a user-defined PL/SQL function that is referenced in the statement attempted to query or modify a table that was in the middle of being modified by the statement that fired the trigger

You are trying to update the same row, which was inserted, in its own trigger and that is the reason for the error. In After Row Insert Trigger, you have to copy the values of COL1 and COL2 into a PL/SQL table. Create a After Statement Insert trigger and get the values from the PL/SQL table and use them for your update.

Hope it explains. Let me know if an example is required.

Regards,
K.Murali

In article <8107nr$1sa1$1_at_news.cz.js.cn>,   "Joseph Chang" <chinastorm_at_bigfoot.com> wrote:
> Hi everybody!
> i am a pl/sql beginner. i have a prblem about trigger.
> the temp table looks like that
> col1(number) col2 (number)
> when i insert a new record to this table, the col2 should be
col1+1000 .
> i try the trigger like that:
>
> create or replace trigger firea
> after insert on temp
> for each row
> declare
> temp_col1 temp._col1%type;
> begin
> update temp set col2=:new.col1+1000 where col1= :new.col1;
> end firea;
>
> it compile ok, but when i insert a record to temp , the oracle said
> ORA-04091: table SM.TEMP is mutating, trigger/function may not see it
> ORA-06512: at "SM.FIREA", line 4
> ORA-04088: error during execution of trigger 'SM.FIREA'
>
> any tip for solve the problem by change this trigger?
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 18 1999 - 07:31:52 CST