Sql Help Kavali

[bokadia]

bye1  bye1

[bokadia]

evaraina unnara

[gandhi]

ltt

[Power Star]

oracle a sql server a  ? help bye1 bye1

[bokadia]

oracle trigger lo eco error vastondi

[Power Star]

oracle trigger lo eco error vastondi

 

call oracle support bye1

[bokadia]

20300, 'ActiveDB Error: cannot update key fields of source table.');

END TRI_P_WH_INV_TRANS;

[gandhi]

call oracle support bye1

 

lol

 

 

ne avva

 

oracle supportcheyadu dood

 

10geyi antaru

[President]

 

20300, 'ActiveDB Error: cannot update key fields of source table.');

END TRI_P_WH_INV_TRANS;

 

nuvvu Primary key fields data ni update chesthunnava? 

[bokadia]

aathcare

call oracle support bye1

 

[bokadia]

s basic gaa merge statement lantidi use chesthunna i am creating a duplicate table and inserting new records in the destination table

nuvvu Primary key fields data ni update chesthunnava? 

 

[MeetFriendz]

aa destination table meedha yee columns meedha primary key or foreign or unique keys vunte choodu ... aa fileds is stopping you to insert new records .. either data marchali with respect to those fields else remove those keys and insert data and think later if those keys are really needed ... 

[pachimirchi]

aa destination table meedha yee columns meedha primary key or foreign or unique keys vunte choodu ... aa fileds is stopping you to insert new records .. either data marchali with respect to those fields else remove those keys and insert data and think later if those keys are really needed ... 

 

agreed...

 

join sarigga cheyyatle or bad data quality.. insert into a temp table and find out duplicate values w.r.to PK  

[bokadia]

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;

/

[Hyderabad_Nawab]

LTT