TRIGGER may not see it
- From: jose soares <jose.soares@xxxxxxxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 11 Feb 2011 14:31:40 +0100
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
Other related posts:
