Re: trigger

>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
>
>

The mutating table error means that you are querying either the same table you are updating, or one connected to it by a foreign key. This is not allowed in a row-level trigger. However, you can do this in a STATEMENT level trigger. To do this, you need to do a few things...
in a BEFORE-STATEMENT trigger, initialize either a PL/SQL table or a real DB table (This will fire once per SQL statement, regardless of if 0 rows, 1 row or 1 million rows are affected)
in the BEFORE-ROW trigger add the PK of the affected row to the PL/SQL table or DB table (fires once per updated row)
in an AFTER-STATEMENT trigger, go through the list of updated rows, and do the updates to the OPERATORS table. Be sure to clear the list of updated rows. (fires once per SQL statement)

If you look at a few books on PL/SQL, they will tell you how to work with PL/SQL tables, which is really the better option.

Hope this helps...mutating tables are a real pain...

Dan Received on Thu Jan 04 2001 - 04:07:16 CET