Re: trigger

Look article "Avoiding Mutating Tables" by Thomas Kyte http://govt.oracle.com/~tkyte/

Regards,
Aleksandr Kononov

In article <3A5352D9.DBA4E674_at_akio-solutions.com>,   claude <blanchet_at_akio-solutions.com> wrote:
>
> Hi,
>
> We got a question about triggers on Oracle.
>
> We are migrating a web application from RDBMS PostgresSQL to RDBMS
> Oracle
> version 8i.
>
> All run on Linux.
>
> The trigger is:
> -- POSTGRESLQ --
> << <<
> CREATE FUNCTION operatorsgroups_update() RETURNS OPAQUE AS '
> DECLARE
> minadmin int4;
> groupid int2;
> BEGIN
> SELECT INTO minadmin min(function_id) FROM operators_groups
> WHERE
> operator_id = NEW.operator_id;
> select into groupid group_id from operators WHERE id =
> NEW.operator_id;
> IF ((minadmin < 10) and (groupid > 10)) THEN
> update operators set group_id=1 where id = NEW.operator_id;
> end if;
> IF ((minadmin = 10) and (groupid < 10)) THEN
> update operators set group_id=500 where id = NEW.operator_id;
> end if;
>
> return NEW;
> END;
> ' LANGUAGE 'plpgsql';
> >> >>
>
> The transation in Oracle is :
> << <<
> create or replace trigger operatorsgroups_update_trigger
> after update on OPERATORS_GROUPS
> for each row
> DECLARE
> minadmin INTEGER;
> groupid INTEGER;
> BEGIN
> select MIN(function_id)
> into minadmin
> from OPERATORS_GROUPS
> where operator_id = :new.operator_id;
>
> IF SQL%NOTFOUND THEN
> minadmin := :new.function_id;
> END IF;
>
> select group_id
> into groupid
> from OPERATORS
> where id = :new.operator_id;
>
> IF ( (minadmin < 10) and (groupid > 10) ) THEN
> update OPERATORS
> set group_id = 1
> where id = :new.operator_id;
> end if;
>
> IF ( (minadmin = 10) and (groupid < 10) ) THEN
> update OPERATORS
> set group_id = 500
> where id = :new.operator_id;
> end if;
>
> END;
> >> >>
>
> We got a error when the trigger is executing in Oracle.
> The message is :
> << <<
> ERROR at line 1:
> ORA-04091: table AKIO.OPERATORS_GROUPS is mutating, trigger/function
 may
> not
> see it
> ORA-06512: at "AKIO.OPERATORSGROUPS_UPDATE_TRIGGER", line 5
> ORA-04088: error during execution of trigger
> 'AKIO.OPERATORSGROUPS_UPDATE_TRIGGER'
> >> >>
>
> In Oracle documentation, it means :
> " A trigger attempted to look at (or modify) a table that was in the
> middle of
> being modified by the statement which fired it.
>
> Action : Rewrite the trigger so it does not read that table.
> "
>
> In our case, we do a select in table OPERATORS_GROUPS in the trigger
> that is
> fired by an insert in the table OPERATORS_GROUPS.
>
> The solution from Oracle means to rewrite all for us.
>
> Who has met this problem and how to resolve at low cost ?
>
> Thank you for us
>
> Prhasak & Claude
>
>

Sent via Deja.com
http://www.deja.com/ Received on Thu Jan 04 2001 - 00:24:22 CST