trigger

From: claude <blanchet_at_akio-solutions.com>
Date: Wed, 03 Jan 2001 17:27:05 +0100
Message-ID: <3A5352D9.DBA4E674@akio-solutions.com>

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 Received on Wed Jan 03 2001 - 10:27:05 CST