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

Hi,

To your problem I wonder why you are doing the select: You don't use your bind variable! Removing the select also removes your problem.

In other cases the answer is not so easy. I found a way to get around the mutating triggers. I don't know if it is genious, but it works! I do it this way:

1. create a controltable, say ctrl_tab. The table must have a layout, which can hold your key-values of the table, you want to create the trigger upon. In your case I guess it's the flight_id column.
2. Now create a trigger on a_table (before, each row): The only thing to do is to make an insert in ctrl_tab with the value of the updated records key-value.
3. Then create another trigger (after, statement), and let this trigger do your work on b_table. Remember there can several records i ctrl_tab (if you're doing update of more rows in a_table in one statement). Also remember to clean up the ctrl_tab in this trigger: It's essential that no records are ever commitet in ctrl_tab!

Hope you can follow the explanations above.

Regards,

Michael Ringbo, mri_at_dde.dk

Novita Leung wrote:

>
>
> Hi,
>
> 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 Thu Feb 19 1998 - 00:00:00 CET