Re: TRIGGER may not see it

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_at_barclayscapital.com> 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> 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
>
> --
> http://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.
> _______________________________________________
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Toon Koppelaars
RuleGen BV
Toon.Koppelaars_at_RuleGen.com
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com

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

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