Re: TRIGGER may not see it
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: jose.soares@xxxxxxxxxxxxxx
- Date: Fri, 11 Feb 2011 13:37:04 +0000
You are selecting from the table you are updating.
On 11 Feb 2011 13:33, "jose soares" <jose.soares@xxxxxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
