> > 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