Re: TRIGGER may not see it

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Fri, 11 Feb 2011 13:37:04 +0000
Message-ID: <AANLkTikMVmmm2vsETFJ0M1kuRQs0pRHkY0j=MxJWzLL2_at_mail.gmail.com>

You are selecting from the table you are updating.

On 11 Feb 2011 13:33, "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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Feb 11 2011 - 07:37:04 CST