Re: TRIGGER may not see it

http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551198119097816936

Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>

On 02/11/2011 02:31 PM, jose soares 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
>
>
>

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