Re: TRIGGER may not see it

  • From: Toon Koppelaars <toon@xxxxxxxxxxx>
  • To: "Dominic.Brooks" <Dominic.Brooks@xxxxxxxxxxxxxxxxxxx>
  • Date: Fri, 11 Feb 2011 15:09:45 +0100

>
> Obviously triggers are evil,


No, no, no...

They are evil when they cause automagic side effects. Which is: when they
contain insert/update/delete statements. Or otherwise do stuff, you don't
expect.

They are *not* evil when they contain queries that (try to) enforce data
integrity constraints that can't be dealt with declaratively.

I'll explain all about that again in a few weeks at the Hotsos symposium.

On Fri, Feb 11, 2011 at 2:40 PM, <Dominic.Brooks@xxxxxxxxxxxxxxxxxxx> wrote:

> You can't select from the table you're updating - not allowed.
>
> Obviously triggers are evil, however, in terms of getting this working, I
> don't think you need to select from scadenziario, do you?
>
> Would this not work?
>
> 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   sopralluogo on :old.id_sopralluogo;
>             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;
>
> Cheers,
> Dominic
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of jose soares
> Sent: 11 February 2011 13:32
> To: ORACLE-L
> Subject: TRIGGER may not see it
>
> 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
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
> _______________________________________________
>
> This e-mail may contain information that is confidential, privileged or
> otherwise protected from disclosure. If you are not an intended recipient of
> this e-mail, do not duplicate or redistribute it by any means. Please delete
> it and any attachments and notify the sender that you have received it in
> error. Unless specifically indicated, this e-mail is not an offer to buy or
> sell or a solicitation to buy or sell any securities, investment products or
> other financial product or service, an official confirmation of any
> transaction, or an official statement of Barclays. Any views or opinions
> presented are solely those of the author and do not necessarily represent
> those of Barclays. This e-mail is subject to terms available at the
> following link: www.barcap.com/emaildisclaimer. By messaging with Barclays
> you consent to the foregoing.  Barclays Capital is the investment banking
> division of Barclays Bank PLC, a company registered in England (number
> 1026167) with its registered offic
>  e at 1 Churchill Place, London, E14 5HP.  This email may relate to or be
> sent from other members of the Barclays Group.
> _______________________________________________
> --
> //www.freelists.org/webpage/oracle-l
>
>
>


-- 
Toon Koppelaars
RuleGen BV
Toon.Koppelaars@xxxxxxxxxxx
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com

(co)Author: "Applied Mathematics for Database Professionals"
www.RuleGen.com/pls/apex/f?p=14265:13

Other related posts: