pachimirchi Posted October 7, 2014 Report Posted October 7, 2014 if update case lo you are inserting a new record with existing PK value... idi incorrect... CREATE OR REPLACE TRIGGER TRI_P_WH_INV_TRANS AFTER INSERT OR DELETE OR UPDATE ON WH_INV_TRANS FOR EACH ROW DECLARE updating_key_fields EXCEPTION; BEGIN IF INSERTING THEN INSERT INTO ICS.P_WH_INV_TRANS VALUES ( :NEW.RRN,--PK :NEW.COSTCENTER, :NEW.STATUS, :NEW.INVENTORYTRANSTYPESK, :NEW.INVENTORYTRANSACTIONTYPE, :NEW.INV_TRANS_TYPE_DESC, :NEW.PRODUCTCODESK, :NEW.PRODUCTCODE, :NEW.PRODUCTCODEDESCRIPTION, :NEW.WAREHOUSESK, :NEW.WAREHOUSENUMBER, :NEW.LOT, :NEW.QUANTITY, :NEW.TRANSACTIONDATE, :NEW.SUBMITTEDTIME, :NEW.INVENTORYEVENTCODESK, :NEW.INVENTORYEVENTCODE, :NEW.INV_EVENT_CD_DESC, :NEW.ORDERNUMBER, :NEW.ORDERLINENUMBER, :NEW.PICKLISTNUMBER, :NEW.PICKLISTLINENUMBER, :NEW.PICKCONSOLIDATIONNUMBER, :NEW.SHIPMENTNUMBER, :NEW.LOTEXISTS, :NEW.ACCOUNTINGPERIOD, :NEW.COST_VAL_OF_INV_TRANS, :NEW.REASONCODESK, :NEW.REASONCODE, :NEW.REASONCODEDESCRIPTION, :NEW.USERPROFILESK, :NEW.SIGNATURE, :NEW.USERID, :NEW.WORKSTATIONIDJOBNAME, :NEW.TRANSPORTNOTENUMBER, :NEW.TRANSACTIONNUMBER, :NEW.TRANSACTIONDATE_TIMECDSK, :NEW.DMDATE, :NEW.DMLIB, :NEW.DMOBJ, :NEW.DMSYS, :NEW.DMUSER, :NEW.DMACTIVITY, :NEW.STAGE_LOADDATE, :NEW.STAGE_INSERTDATE, :NEW.LAST_INSERT_DATE, :NEW.LAST_UPDATE_DATE, NULL, P_WH_INV_TRANS_SEQ.NEXTVAL, 0, SYSTIMESTAMP, 1, NULL, NULL, 'N', -1, NULL); END IF; IF UPDATING THEN IF UPDATING('RRN') THEN RAISE updating_key_fields; END IF; INSERT INTO ICS.P_WH_INV_TRANS VALUES ( :OLD.RRN, :NEW.COSTCENTER, :NEW.STATUS, :NEW.INVENTORYTRANSTYPESK, :NEW.INVENTORYTRANSACTIONTYPE, :NEW.INV_TRANS_TYPE_DESC, :NEW.PRODUCTCODESK, :NEW.PRODUCTCODE, :NEW.PRODUCTCODEDESCRIPTION, :NEW.WAREHOUSESK, :NEW.WAREHOUSENUMBER, :NEW.LOT, :NEW.QUANTITY, :NEW.TRANSACTIONDATE, :NEW.SUBMITTEDTIME, :NEW.INVENTORYEVENTCODESK, :NEW.INVENTORYEVENTCODE, :NEW.INV_EVENT_CD_DESC, :NEW.ORDERNUMBER, :NEW.ORDERLINENUMBER, :NEW.PICKLISTNUMBER, :NEW.PICKLISTLINENUMBER, :NEW.PICKCONSOLIDATIONNUMBER, :NEW.SHIPMENTNUMBER, :NEW.LOTEXISTS, :NEW.ACCOUNTINGPERIOD, :NEW.COST_VAL_OF_INV_TRANS, :NEW.REASONCODESK, :NEW.REASONCODE, :NEW.REASONCODEDESCRIPTION, :NEW.USERPROFILESK, :NEW.SIGNATURE, :NEW.USERID, :NEW.WORKSTATIONIDJOBNAME, :NEW.TRANSPORTNOTENUMBER, :NEW.TRANSACTIONNUMBER, :NEW.TRANSACTIONDATE_TIMECDSK, :NEW.DMDATE, :NEW.DMLIB, :NEW.DMOBJ, :NEW.DMSYS, :NEW.DMUSER, :NEW.DMACTIVITY, :NEW.STAGE_LOADDATE, :NEW.STAGE_INSERTDATE, :NEW.LAST_INSERT_DATE, :NEW.LAST_UPDATE_DATE, NULL, ICS.P_WH_INV_TRANS_SEQ.NEXTVAL, 0, SYSTIMESTAMP, 2, NULL, NULL, 'N', -1, NULL); END IF; IF DELETING THEN INSERT INTO ICS.P_WH_INV_TRANS VALUES ( :OLD.RRN, :OLD.COSTCENTER, :OLD.STATUS, :OLD.INVENTORYTRANSTYPESK, :OLD.INVENTORYTRANSACTIONTYPE, :OLD.INV_TRANS_TYPE_DESC, :OLD.PRODUCTCODESK, :OLD.PRODUCTCODE, :OLD.PRODUCTCODEDESCRIPTION, :OLD.WAREHOUSESK, :OLD.WAREHOUSENUMBER, :OLD.LOT, :OLD.QUANTITY, :OLD.TRANSACTIONDATE, :OLD.SUBMITTEDTIME, :OLD.INVENTORYEVENTCODESK, :OLD.INVENTORYEVENTCODE, :OLD.INV_EVENT_CD_DESC, :OLD.ORDERNUMBER, :OLD.ORDERLINENUMBER, :OLD.PICKLISTNUMBER, :OLD.PICKLISTLINENUMBER, :OLD.PICKCONSOLIDATIONNUMBER, :OLD.SHIPMENTNUMBER, :OLD.LOTEXISTS, :OLD.ACCOUNTINGPERIOD, :OLD.COST_VAL_OF_INV_TRANS, :OLD.REASONCODESK, :OLD.REASONCODE, :OLD.REASONCODEDESCRIPTION, :OLD.USERPROFILESK, :OLD.SIGNATURE, :OLD.USERID, :OLD.WORKSTATIONIDJOBNAME, :OLD.TRANSPORTNOTENUMBER, :OLD.TRANSACTIONNUMBER, :OLD.TRANSACTIONDATE_TIMECDSK, :OLD.DMDATE, :OLD.DMLIB, :OLD.DMOBJ, :OLD.DMSYS, :OLD.DMUSER, :OLD.DMACTIVITY, :OLD.STAGE_LOADDATE, :OLD.STAGE_INSERTDATE, :OLD.LAST_INSERT_DATE, :OLD.LAST_UPDATE_DATE, NULL, ICS.P_WH_INV_TRANS_SEQ.NEXTVAL, 0, SYSTIMESTAMP, 3, NULL, NULL, 'N', -1, NULL); END IF; EXCEPTION WHEN updating_key_fields THEN raise_application_error(-20300, 'ActiveDB Error: cannot update key fields of source table.'); END TRI_P_WH_INV_TRANS; /
bokadia Posted October 7, 2014 Author Report Posted October 7, 2014 k got it bhayya i only need to insert new records can i delete the update and delete from the sql statement if update case lo you are inserting a new record with existing PK value... idi incorrect...
bokadia Posted October 7, 2014 Author Report Posted October 7, 2014 CREATE OR REPLACE TRIGGER TRI_P_WH_INV_TRANS AFTER INSERT OR DELETE OR UPDATE ON WH_INV_TRANS FOR EACH ROW DECLARE updating_key_fields EXCEPTION; BEGIN IF INSERTING THEN INSERT INTO ICS.P_WH_INV_TRANS VALUES ( :NEW.RRN,--PK :NEW.COSTCENTER, :NEW.STATUS, :NEW.INVENTORYTRANSTYPESK, :NEW.INVENTORYTRANSACTIONTYPE, :NEW.INV_TRANS_TYPE_DESC, :NEW.PRODUCTCODESK, :NEW.PRODUCTCODE, :NEW.PRODUCTCODEDESCRIPTION, :NEW.WAREHOUSESK, :NEW.WAREHOUSENUMBER, :NEW.LOT, :NEW.QUANTITY, :NEW.TRANSACTIONDATE, :NEW.SUBMITTEDTIME, :NEW.INVENTORYEVENTCODESK, :NEW.INVENTORYEVENTCODE, :NEW.INV_EVENT_CD_DESC, :NEW.ORDERNUMBER, :NEW.ORDERLINENUMBER, :NEW.PICKLISTNUMBER, :NEW.PICKLISTLINENUMBER, :NEW.PICKCONSOLIDATIONNUMBER, :NEW.SHIPMENTNUMBER, :NEW.LOTEXISTS, :NEW.ACCOUNTINGPERIOD, :NEW.COST_VAL_OF_INV_TRANS, :NEW.REASONCODESK, :NEW.REASONCODE, :NEW.REASONCODEDESCRIPTION, :NEW.USERPROFILESK, :NEW.SIGNATURE, :NEW.USERID, :NEW.WORKSTATIONIDJOBNAME, :NEW.TRANSPORTNOTENUMBER, :NEW.TRANSACTIONNUMBER, :NEW.TRANSACTIONDATE_TIMECDSK, :NEW.DMDATE, :NEW.DMLIB, :NEW.DMOBJ, :NEW.DMSYS, :NEW.DMUSER, :NEW.DMACTIVITY, :NEW.STAGE_LOADDATE, :NEW.STAGE_INSERTDATE, :NEW.LAST_INSERT_DATE, :NEW.LAST_UPDATE_DATE, NULL, P_WH_INV_TRANS_SEQ.NEXTVAL, 0, SYSTIMESTAMP, 1, NULL, NULL, 'N', -1, NULL); END TRI_P_WH_INV_TRANS; saripothunda
pachimirchi Posted October 7, 2014 Report Posted October 7, 2014 shouldn't cause previous error...kaani ikkada trigger purpose enti bhayya.. insert kante update and delete makes more sense to use trigger (to capture data changes/ journaling) in this scenario.. you the best person who can answer that... CREATE OR REPLACE TRIGGER TRI_P_WH_INV_TRANS AFTER INSERT OR DELETE OR UPDATE ON WH_INV_TRANS FOR EACH ROW DECLARE updating_key_fields EXCEPTION; BEGIN IF INSERTING THEN INSERT INTO ICS.P_WH_INV_TRANS VALUES ( :NEW.RRN,--PK :NEW.COSTCENTER, :NEW.STATUS, :NEW.INVENTORYTRANSTYPESK, :NEW.INVENTORYTRANSACTIONTYPE, :NEW.INV_TRANS_TYPE_DESC, :NEW.PRODUCTCODESK, :NEW.PRODUCTCODE, :NEW.PRODUCTCODEDESCRIPTION, :NEW.WAREHOUSESK, :NEW.WAREHOUSENUMBER, :NEW.LOT, :NEW.QUANTITY, :NEW.TRANSACTIONDATE, :NEW.SUBMITTEDTIME, :NEW.INVENTORYEVENTCODESK, :NEW.INVENTORYEVENTCODE, :NEW.INV_EVENT_CD_DESC, :NEW.ORDERNUMBER, :NEW.ORDERLINENUMBER, :NEW.PICKLISTNUMBER, :NEW.PICKLISTLINENUMBER, :NEW.PICKCONSOLIDATIONNUMBER, :NEW.SHIPMENTNUMBER, :NEW.LOTEXISTS, :NEW.ACCOUNTINGPERIOD, :NEW.COST_VAL_OF_INV_TRANS, :NEW.REASONCODESK, :NEW.REASONCODE, :NEW.REASONCODEDESCRIPTION, :NEW.USERPROFILESK, :NEW.SIGNATURE, :NEW.USERID, :NEW.WORKSTATIONIDJOBNAME, :NEW.TRANSPORTNOTENUMBER, :NEW.TRANSACTIONNUMBER, :NEW.TRANSACTIONDATE_TIMECDSK, :NEW.DMDATE, :NEW.DMLIB, :NEW.DMOBJ, :NEW.DMSYS, :NEW.DMUSER, :NEW.DMACTIVITY, :NEW.STAGE_LOADDATE, :NEW.STAGE_INSERTDATE, :NEW.LAST_INSERT_DATE, :NEW.LAST_UPDATE_DATE, NULL, P_WH_INV_TRANS_SEQ.NEXTVAL, 0, SYSTIMESTAMP, 1, NULL, NULL, 'N', -1, NULL); END TRI_P_WH_INV_TRANS; saripothunda
bokadia Posted October 7, 2014 Author Report Posted October 7, 2014 basically naaku WH_INV_TRANS table lo data insert chestaru everyday.. i don't know a time lo insert chestaro.. so nenu tibco db adapter use chesi ee trigger generate chesa so everytime records insert cheyyagane adapter insert ayina new recods ni vere table ki publish cheyyali shouldn't cause previous error...kaani ikkada trigger purpose enti bhayya.. insert kante update and delete makes more sense to use trigger (to capture data changes/ journaling) in this scenario.. you the best person who can answer that...
pachimirchi Posted October 7, 2014 Report Posted October 7, 2014 basically naaku WH_INV_TRANS table lo data insert chestaru everyday.. i don't know a time lo insert chestaro.. so nenu tibco db adapter use chesi ee trigger generate chesa so everytime records insert cheyyagane adapter insert ayina new recods ni vere table ki publish cheyyali ah I see.. aite updates and deletes kooda capture cheyyali ga as you had before.. aina oka view or synonym create cheste saripoddi kada.. y this kolaveri? ila adugutooone vunta gaani lite tesko bhayya..
Hyderabad_Nawab Posted October 7, 2014 Report Posted October 7, 2014 LTT Ee Luv da Adapters sava10gentunattundhi.. ;)
bokadia Posted October 7, 2014 Author Report Posted October 7, 2014 LTT Ee Luv da Adapters sava10gentunattundhi.. ;) publish avuthondi baa new table meeda but aa publish table nundi pick avvadam ledhu
Recommended Posts