Re: TRIGGER may not see it

From: Subodh Deshpande <deshpande.subodh_at_gmail.com>
Date: Fri, 11 Feb 2011 19:10:00 +0530
Message-ID: <AANLkTi=SeKOL_ToCr=1aUC0+nx1c2V4u0qizJNHT182o_at_mail.gmail.com>

this is standard table mutuating error..check the triggering event...

On 11 February 2011 19:01, jose soares <jose.soares_at_sferacarta.com> wrote:

> Hi all,
>
> I created a trigger in my db with no errors, like this one:
>
> CREATE OR REPLACE TRIGGER scadenziario_update_trigger
> BEFORE UPDATE ON scadenziario
> FOR EACH ROW
> DECLARE
> motivo_sopralluogo sopralluogo.cod_motivo_sopralluogo%%type;
> BEGIN
> SELECT sopralluogo.cod_motivo_sopralluogo INTO motivo_sopralluogo
> FROM scadenziario join sopralluogo on scadenziario.id_sopralluogo
> = sopralluogo.id
> WHERE scadenziario.id = :old.id ;
> IF motivo_sopralluogo = 'PRO' AND :new.id_piano IS NULL AND
> :old.id_piano IS NOT NULL THEN
> RAISE_APPLICATION_ERROR(-20000, 'error trigger 212');
> END IF;
> END;
>
>
> when I try this query:
>
> UPDATE scadenziario
> SET id_veterinario_programmato=637
> WHERE id_unita_aziendale=36930
> AND scadenziario.esito IS NULL
> AND id_piano=23
> AND id_veterinario_programmato=321
>
> it says:
>
> cx_Oracle.DatabaseError: ORA-04091: table JOSE.SCADENZIARIO is mutating,
> trigger/function may not see it
> ORA-06512: at "JOSE.SCADENZIARIO_UPDATE_TRIGGER", line 4
> ORA-04088: error during execution of trigger
> 'JOSE.SCADENZIARIO_UPDATE_TRIGGER'
>
> I can't see what's wrong with it.
> Could someone, please, get me some help?
>
> Thank you in advance.
>
> j
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
==============================
DO NOT FORGET TO SMILE TODAY
==============================

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 11 2011 - 07:40:00 CST