Home » SQL & PL/SQL » SQL & PL/SQL » mutating error (merged)
mutating error (merged) [message #404754] Sat, 23 May 2009 06:14 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

I AM TRYING TO WRITE ONE TRIGGER AFTTER INSERT ON ONE TABLE OT_MAT_ISS_ITEM THIS WILL INSERT VALUES OF THIS TABLE INTO ANOTHER TABLE OT_MAT_ISS_BATCH AS BELOW

CREATE OR REPLACE TRIGGER ORION2007.AIC_MIB AFTER INSERT
ON ORION2007.OT_MAT_ISS_ITEM FOR EACH ROW
DECLARE
M_miB_SYS_ID NUMBER;
CURSOR C1 IS
SELECT RI_CHARGE_CODE BATCH_NO,RI_ITEM_CODE,RI_qty_bu,RI_QTY,RI_QTY_LS,RI_CR_UID,MII_SYS_ID
FROM OT_REQ_ITEM,OM_ITEM,OT_MAT_ISS_ITEM
WHERE RI_SYS_ID=:NEW.mii_RI_SYS_ID
AND RI_CHARGE_AREA_NUM=1
AND RI_ITEM_CODE=ITEM_CODE
AND ITEM_BATCH_COSTING_YN_NUM =1
AND ITEM_STK_YN_NUM=1;
BEGIN
FOR I IN C1
LOOP
SELECT mib_SYS_ID.NEXTVAL INTO M_miB_SYS_ID FROM DUAL;
INSERT INTO OT_MAT_ISS_BATCH
(miB_SYS_ID,
miB_miI_SYS_ID,
MIB_WHOB_SYS_ID,
miB_BATCH_NO,
miB_QTY,
miB_QTY_LS,
miB_QTY_BU,
MIB_RESV_UTIL_BU,
MIB_WHOB_QTY_BU,
MIB_CR_DT,
MIB_cR_UID,
MIB_UPD_DT,
MIB_UPD_UID )
VALUES
(
M_MIB_SYS_ID,
:NEW.MII_SYS_ID,
NULL,
I.BATCH_NO ,
I.RI_QTY ,
I.RI_QTY_LS,
I.RI_QTY_BU,
NULL,
NULL,
SYSDATE,
I.RI_CR_UID,
NULL,
NULL) ;
END LOOP ;
END;
/

TABLE OT_MAT_ISS_ITEM IS MUTATING
TRIGGER/FUNCTION MAY NOT SEE IT
ORA-40508 - UNKNOWN ERROR ORACLE UNABLE TO INSERT RECORD
Re: mutating error (merged) [message #404767 is a reply to message #404754] Sat, 23 May 2009 09:38 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't post in UPPER case but format your code as already been asked to you.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Regards
Michel

Previous Topic: TO know when the table is updated
Next Topic: PARTITIONS missing
Goto Forum:
  


Current Time: Sat Dec 03 20:12:00 CST 2016

Total time taken to generate the page: 0.05581 seconds