I need help on this. I want to create a trigger that should fire before update(back end) when an insert occurs on a table(front end)but the back end table has one of it column datatype(NVARCH AR2(2) different from the front end datatype(VARCHA R2(35)table and as such the trigger is not working. So i introduced a case function
so as to allow update on the backend table but i keep getting bad bind variable.
Here is what my code looks like:
This is the error i got:
Code:
IF length(COUNTRY_ORIGIN)= 2 then
COUNTRY_ORIGIN := :new.COUNTRY_ORIGIN;
ELSE
COUNTRY_ORIGIN= null;
Here is what my code looks like:
Code:
alter session set current_schema=TRADEVIEW2;
ALTER TRIGGER "TRADEVIEW2"."TRIG_HEADERS" DISABLE;
create or replace trigger TRADEVIEW2.TRIG_PO_HEADERS
BEFORE INSERT OR UPDATE OR DELETE ON TRADEVIEW2.PO_HEADERS REFERENCING OLD AS old NEW AS new
FOR EACH ROW
DECLARE
currId NUMBER := 0;
p_err_msg varchar2(400) := '';
v_module varchar2(12) := '';
v_COUNTRY_ORIGIN varchar2(2);
BEGIN
DBMS_OUTPUT.PUT_LINE ('Before classifying the event');
IF DELETING THEN
if (:old.LAST_UPD_SRC in ('TX', 'TW')) then
INSERT INTO TWORKS_R2.ITF_TX_PO_HEADER@TWKSUPDATE_LINK
(ID
, REFERENCE_NUMBER
, BUYER_ID
, ENTRY_TIME
, PO_CREATE_DATE
, CURRENCY_CODE
, PO_CONTRACT_NO
, PRODUCT_ID
, INSTRUMENT_TYPE
, LATE_SHIP_DATE
, EARLY_SHIP_DATE
, SHIPPING_TERMS
, SHIPPING_METHOD
, SHIP_FROM_PORT
, SHIP_TO_PORT
, COUNTRY_ORIGIN
, PAY_TYPE
, FOB_DESCRIPTION
, TERMS_TYPE_CODE
, SOURCE
, EVENT
, PICKED_UP
, FAILURE_STATUS
, TX_PAY_TYPE
, GUID
, UPLOAD_FILE_NAME
)
select
TWORKS_R2.SEQ_ITF_TX_PO_HEADER.nextval@TWKSUPDATE_LINK
, :old.POREF
, :old.APPLICANT_ID
, SYSDATE
, :old.PO_DATE
, :old.CURRENCY_CODE
, :old.PO_CONTRACT_NO
, null
, :old.INSTRUMENT_TYPE
, :old.LATE_SHIP_DATE
, :old.EARLY_SHIP_DATE
, :old.SHIPPING_TERMS
, :old.SHIPPING_METHOD
, :old.SHIP_FROM_PORT
, :old.SHIP_TO_PORT
, :old.COUNTRY_ORIGIN
, null
, :old.FOB_DESCRIPTION
, :old.TERMS_TYPE_CODE
, 'TX'
, 'DELETE'
, 'N'
, null
,:old.PAYMENT_METHOD
,:old.GUID
from dual where not exists (
select 1 from TRADEVIEW2.PO_DELETED
where POREF = :old.POREF
and GUID = :old.GUID
and IS_HEADER = 'Y'
);
END;
/
ELSE
--Insert Logic here for INSERT AND UPDATE
--Handle country_origin length
CASE
WHEN length(COUNTRY_ORIGIN)= 2 then
v_COUNTRY_ORIGIN :='new.COUNTRY_ORIGIN'
new.COUNTRY_ORIGIN :='COUNTRY_ORIGIN';
else
v_COUNTRY_ORIGIN :=null;
:new.COUNTRY_ORIGIN :=null;
END CASE;
--Handle country_origin
IF length(COUNTRY_ORIGIN)= 2 THEN
update TRADEVIEW2.PO_LINEITEMS
set COUNTRY_ORIGIN = :new.COUNTRY_ORIGIN
where POREF = :new.POREF
and GUID = :new.GUID
and (COUNTRY_ORIGIN is null);
END IF;
--Handle switching of product id
CASE
WHEN :new.MODULE = 'COLL' then
v_module := 'COLLECTIONS';
:new.INSTRUMENT_TYPE := 'IMP-OPEN-ACCNT';
WHEN :new.MODULE = 'IMLC' then
v_module := 'COMM-LC';
:new.INSTRUMENT_TYPE :='IMPORT-LC';
WHEN :new.MODULE = 'COLLECTIONS' then --if coming from TW
v_module := 'COLLECTIONS';
:new.MODULE := 'COLL';
WHEN :new.MODULE = 'COMM-LC' then --if coming from TW
v_module := 'COMM-LC';
:new.MODULE := 'IMLC';
else
v_module := null;
:new.INSTRUMENT_TYPE := null;
END CASE;
--Handle product
IF (:new.MODULE is not NULL or :new.MODULE <> '') THEN
update TRADEVIEW2.PO_LINEITEMS
set BUYER_PRODUCT_ID = :new.MODULE
where POREF = :new.POREF
and GUID = :new.GUID
and (BUYER_PRODUCT_ID is null or BUYER_PRODUCT_ID = '');
END IF;
IF INSERTING THEN
if (:new.LAST_UPD_SRC = 'TX') then
BEGIN
INSERT INTO TWORKS_R2.ITF_TX_PO_HEADER@TWKSUPDATE_LINK
(ID
, REFERENCE_NUMBER
, BUYER_ID
, ENTRY_TIME
, PO_CREATE_DATE
, CURRENCY_CODE
, PO_CONTRACT_NO
, PRODUCT_ID
, INSTRUMENT_TYPE
, LATE_SHIP_DATE
, EARLY_SHIP_DATE
, SHIPPING_TERMS
, SHIPPING_METHOD
, SHIP_FROM_PORT
, SHIP_TO_PORT
, COUNTRY_ORIGIN
, PAY_TYPE
, FOB_DESCRIPTION
, TERMS_TYPE_CODE
, SOURCE
, EVENT
, PICKED_UP
, FAILURE_STATUS
, TX_PAY_TYPE
, GUID
, UPLOAD_FILE_NAME
)
VALUES
(
TWORKS_R2.SEQ_ITF_TX_PO_HEADER.nextval@TWKSUPDATE_LINK
, :new.POREF
, :new.APPLICANT_ID
, SYSDATE
, :new.PO_DATE
, :new.CURRENCY_CODE
, :new.PO_CONTRACT_NO
, v_module
, :new.INSTRUMENT_TYPE
, :new.LATE_SHIP_DATE
, :new.EARLY_SHIP_DATE
, :new.SHIPPING_TERMS
, :new.SHIPPING_METHOD
, :new.SHIP_FROM_PORT
, :new.SHIP_TO_PORT
, :new.COUNTRY_ORIGIN
, null
, :new.FOB_DESCRIPTION
, :new.TERMS_TYPE_CODE
, 'TX'
, 'ADD'
, 'N'
, null
, :new.PAYMENT_METHOD
, :new.GUID
, :new.UPLOAD_FILE_NAME
);
select TWORKS_R2.SEQ_ITF_TX_PO_HEADER.currval@TWKSUPDATE_LINK into currId from dual;
:new.ITF_ID := currId;
END;
/
-- Insert Logic here for INSERT ONLY
IF UPDATING THEN
if (:new.LAST_UPD_SRC = 'TX') then
BEGIN
DBMS_OUTPUT.PUT_LINE ('Normal update block');
INSERT INTO TWORKS_R2.ITF_TX_PO_HEADER@TWKSUPDATE_LINK
(ID
, REFERENCE_NUMBER
, BUYER_ID
, ENTRY_TIME
, PO_CREATE_DATE
, CURRENCY_CODE
, PO_CONTRACT_NO
, PRODUCT_ID
, INSTRUMENT_TYPE
, LATE_SHIP_DATE
, EARLY_SHIP_DATE
, SHIPPING_TERMS
, SHIPPING_METHOD
, SHIP_FROM_PORT
, SHIP_TO_PORT
, COUNTRY_ORIGIN
, PAY_TYPE
, FOB_DESCRIPTION
, TERMS_TYPE_CODE
, SOURCE
, EVENT
, PICKED_UP
, FAILURE_STATUS
, TX_PAY_TYPE
, GUID
, UPLOAD_FILE_NAME
)
VALUES
(
TWORKS_R2.SEQ_ITF_TX_PO_HEADER.nextval@TWKSUPDATE_LINK
, :new.POREF
, :new.APPLICANT_ID
, SYSDATE
, :new.PO_DATE
, :new.CURRENCY_CODE
, :new.PO_CONTRACT_NO
, v_module
, :new.INSTRUMENT_TYPE
, :new.LATE_SHIP_DATE
, :new.EARLY_SHIP_DATE
, :new.SHIPPING_TERMS
, :new.SHIPPING_METHOD
, :new.SHIP_FROM_PORT
, :new.SHIP_TO_PORT
, :new.COUNTRY_ORIGIN
, null
, :new.FOB_DESCRIPTION
, :new.TERMS_TYPE_CODE
, 'TX'
, 'UPDATE'
, 'N'
, null
, :new.PAYMENT_METHOD
, :new.GUID
);
select TWORKS_R2.SEQ_ITF_TX_PO_HEADER.currval@TWKSUPDATE_LINK into currId from dual;
:new.ITF_ID := currId;
END;
/
-- Insert Logic here for UPDATE ONLY
IF length(COUNTRY_ORIGIN)= 2 then
v_COUNTRY_ORIGIN := :new.COUNTRY_ORIGIN;
ELSE
COUNTRY_ORIGIN= null;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Other Errors' || SQLERRM);
p_err_msg := SUBSTR (SQLERRM, 1, 400);
INSERT INTO TRADEVIEW2.WF_TX_MSG_ERROR_LOG (TIMESTAMP, SOURCE,MESSAGE, MSG_TEXT)
VALUES (SYSDATE,'Trigger, PO_HEADERS_EVENT','Other Errors', p_err_msg);
END;
ALTER TRIGGER "TRADEVIEW2"."TRIG_HEADERS" ENABLE;
Code:
SQL> show errors
Errors for TRIGGER TRADEVIEW2.TRIG_PO_HEADERS:
LINE/COL ERROR
-------- -----------------------------------------------------------------
65/7 PLS-00049: bad bind variable 'OLD.UPLOAD_FILE_NAME'
162/28 PLS-00049: bad bind variable 'V_COUNTRY_ORIGIN'
172/10 PLS-00049: bad bind variable 'NEW.UPLOAD_FILE_NAME'
231/40 PLS-00049: bad bind variable 'V_COUNTRY_ORIGIN'
241/13 PLS-00049: bad bind variable 'NEW.UPLOAD_FILE_NAME'
250/13 PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
of the following:
( begin case declare else elsif end exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch lock
LINE/COL ERROR
-------- -----------------------------------------------------------------
insert open rollback savepoint set sql execute commit forall
merge pipe purge
257/12 PLS-00103: Encountered the symbol "end-of-file" when expecting
one of the following:
end not pragma final instantiable order overriding static
member constructor map