Triggers and Mutating tables.

From: Jim Ley <jim_at_jibbering.com>
Date: Mon, 14 Aug 2000 14:09:01 GMT
Message-ID: <3997fd52.19756581@news-east.usenetserver.com>

I am using Oracle 7.

This trigger;

create or replace trigger top_level_scope before insert
on composite_enquiry_def
for each row
declare
erro integer;
no_top_level_scope exception;
begin
if (:new.scope != 'ACC') then

   select count(*) into erro from composite_enquiry_def

      where type = :new.type and scope = 'ACC';
      if (erro = 0) then
         raise no_top_level_scope;
     end if;

   else
   dbms_output.put_line('New record inserted.');    end if;
exception
when no_top_level_scope then

   raise_application_error(-20002,'A record of this type with scope = ''ACC'' must exist before further records are created.');

   end;
   /

works perfectly as a before insert trigger on the table. However, if I put 'before update on scope' in place of `before insert` the trigger compiles fine but on update of the table, I get the error;

SQL> update composite_enquiry_def
  2 set scope = 'PHY_CUS' where type = 'INVESTOR';   update composite_enquiry_def

         *
ERROR at line 1:
ORA-04091: table ISET.COMPOSITE_ENQUIRY_DEF is mutating, trigger/function may
not see it
ORA-06512: at "ISET.TOP_LEVEL_SCOPE", line 6 ORA-04088: error during execution of trigger 'ISET.TOP_LEVEL_SCOPE'

The trigger shouldn`t change the table, so I don`t know why it should mutate it. I wondered whether you could tell me why I get this error and a way around it?

Thanks,

Jim. Received on Mon Aug 14 2000 - 09:09:01 CDT