Home » SQL & PL/SQL » SQL & PL/SQL » Error
Error [message #212812] Mon, 08 January 2007 07:55 Go to next message
mesut35
Messages: 1
Registered: January 2007
Location: turkey
Junior Member
Please help !
i have 2 table A and B.
i create a trigger on a table.this trigger insert new record to A table with the data from B table.
but it gives an error as

SQL Server Error: ORA-04091: table DENEK.TBLISEMRI is mutating, trigger/function may not see it
ORA-06512: at "DENEK.ISEMRI", line 13
ORA-04088: error during execution of trigger 'DENEK.ISEMRI'

the trigger is that;

CREATE OR REPLACE TRIGGER "DENEK"."ISEMRI" AFTER
INSERT ON "DENEK"."TBLISEMRI" FOR EACH ROW DECLARE
SAYTUT VARCHAR2(15);

BEGIN

SELECT NVL(PST_ISEMRI.MAMUL_KODU,'') INTO SAYTUT FROM PST_ISEMRI WHERE PST_ISEMRI.DURUM=0 AND PST_ISEMRI.MAMUL_KODU= :NEW.STOK_KODU;

IF NOT SAYTUT='' THEN

UPDATE PST_ISEMRI SET PST_ISEMRI.DURUM=1 WHERE PST_ISEMRI.MAMUL_KODU= :NEW.STOK_KODU;

Insert Into TBLISEMRI (ISEMRINO,TARIH,STOK_KODU,MIKTAR,TESLIM_TARIHI,KAPALI,ONCELIK,YAPKOD,KAYITYAPANKUL,KAYITTARIHI)
Values ('009999999899497','03/01/2007',
(SELECT PST_ISEMRI.YARI_MAMUL_KODU FROM PST_ISEMRI WHERE PST_ISEMRI.MAMUL_KODU= :NEW.STOK_KODU),
10.0,'04/01/2007', 'H',0,NULL,'MKARAMAN',To_Date('03/01/2007 10:24:42','dd/mm/yyyy hh24:mi:ss'));

END IF;
END;

can you help me?????
Re: Error [message #212823 is a reply to message #212812] Mon, 08 January 2007 08:12 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Search the board or in google.com for the classic error of "mutating tables" and you'll find thousands upon thousands of examples and solutions.

And, these are not DATEs, with the exception of the TO_DATE value:
Quote:

Insert Into TBLISEMRI (ISEMRINO,TARIH,STOK_KODU,MIKTAR,TESLIM_TARIHI,KAPALI,ONCELIK,YAPKOD,KAYITYAPANKUL,KAYITTARIHI)
Values ('009999999899497','03/01/2007',
(SELECT PST_ISEMRI.YARI_MAMUL_KODU FROM PST_ISEMRI WHERE PST_ISEMRI.MAMUL_KODU= :NEW.STOK_KODU),
10.0,'04/01/2007', 'H',0,NULL,'MKARAMAN',To_Date('03/01/2007 10:24:42','dd/mm/yyyy hh24:mi:ss'));

[Updated on: Mon, 08 January 2007 08:14]

Report message to a moderator

Previous Topic: Different explain plan in a similar DBs
Next Topic: Case/Decode
Goto Forum:
  


Current Time: Thu Dec 08 02:26:03 CST 2016

Total time taken to generate the page: 0.12512 seconds