Hi,
Have an AFTER INSERT trigger below which populates another table ONLY if a condition is met. Want to CLEAR the prior trigger commands if that condition is NOT met. What is the best way to do this ?
Other triggers on the table are not saving a record in (this table) due to my trigger.
Thanks, Steve.
DECLARE
mSHIPPED_DATE SYSADM.SHIPPER.SHIPPED_DATE%type;
mPACKLIST_ID SYSADM.SHIPPER.PACKLIST_ID%type;
mLINE_NO SYSADM.SHIPPER_LINE.LINE_NO%type;
mCUST_ORDER_ID SYSADM.SHIPPER_LINE.CUST_ORDER_ID%type;
mCUST_ORDER_LINE_NO SYSADM.SHIPPER_LINE.CUST_ORDER_LINE_NO%type;
mORDER_QTY SYSADM.CUST_ORDER_LINE.ORDER_QTY%type;
mUSER_SHIPPED_QTY SYSADM.SHIPPER_LINE.USER_SHIPPED_QTY%type;
mQTY_BO varchar2(10);
mDESCRIPTION SYSADM.PART.DESCRIPTION%type;
mCUSTOMER_ID SYSADM.CUSTOMER_ORDER.CUSTOMER_ID%type;
mCUSTOMER_PO_REF SYSADM.CUSTOMER_ORDER.CUSTOMER_PO_REF%type;
mSHIPPED_TIME varchar2(30);
mseqNo NUMBER;
mPART_ID SYSADM.CUST_ORDER_LINE.PART_ID%type;
mTOTALSHIPPEDQTY SYSADM.CUST_ORDER_LINE.TOTAL_SHIPPED_QTY%type;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT
SYSADM.SHIPPER.SHIPPED_DATE,
SYSADM.SHIPPER.PACKLIST_ID,
:OLDDATA.LINE_NO,
:OLDDATA.CUST_ORDER_ID,
:OLDDATA.CUST_ORDER_LINE_NO,
SYSADM.CUST_ORDER_LINE.ORDER_QTY,
:OLDDATA.USER_SHIPPED_QTY,
SYSADM.PART.DESCRIPTION,
SYSADM.CUSTOMER_ORDER.CUSTOMER_ID,
SYSADM.CUSTOMER_ORDER.CUSTOMER_PO_REF,
SYSADM.SHIPPER.SHIPPED_TIME,
SYSADM.CUST_ORDER_LINE.PART_ID,
SYSADM.CUST_ORDER_LINE.TOTAL_SHIPPED_QTY
INTO mSHIPPED_DATE, mPACKLIST_ID, mLINE_NO, mCUST_ORDER_ID, mCUST_ORDER_LINE_NO, mORDER_QTY, mUSER_SHIPPED_QTY, mDESCRIPTION, mCUSTOMER_ID, mCUSTOMER_PO_REF, mSHIPPED_TIME, mPART_ID, mTOTALSHIPPEDQTY
FROM SYSADM.SHIPPER, SYSADM.CUST_ORDER_LINE, SYSADM.CUSTOMER_ORDER, SYSADM.CUSTOMER, SYSADM.PART
WHERE SYSADM.SHIPPER.PACKLIST_ID= :OLDDATA.PACKLIST_ID
AND :OLDDATA.CUST_ORDER_ID = SYSADM.CUST_ORDER_LINE.CUST_ORDER_ID
AND :OLDDATA.CUST_ORDER_LINE_NO = SYSADM.CUST_ORDER_LINE.LINE_NO
AND SYSADM.CUSTOMER_ORDER.CUSTOMER_ID = SYSADM.CUSTOMER.ID
AND SYSADM.CUST_ORDER_LINE.CUST_ORDER_ID = SYSADM.CUSTOMER_ORDER.ID
AND SYSADM.CUST_ORDER_LINE.PART_ID = SYSADM.PART.ID;
IF mCUSTOMER_ID='97' AND mUSER_SHIPPED_QTY >0 THEN
SELECT TBLSDSHIPMENTS_SEQUENCE.nextval INTO mseqNo FROM dual;
INSERT INTO TBLSDSHIPMENTS (SDID, SHIPPED_DATE, PACKLIST_ID, LINE_NO, CUST_ORDER_ID, CUST_ORDER_LINE_NO, ORDER_QTY, USER_SHIPPED_QTY, QTY_BO, DESCRIPTION, CUSTOMER_ID, CUSTOMER_PO_REF, SHIPPED_TIME, SDSHIPMENTSTATUS, PART_ID)
VALUES (mseqNo, mSHIPPED_DATE, mPACKLIST_ID, mLINE_NO, mCUST_ORDER_ID, mCUST_ORDER_LINE_NO, mORDER_QTY, mUSER_SHIPPED_QTY, mORDER_QTY - (mTOTALSHIPPEDQTY + mUSER_SHIPPED_QTY), mDESCRIPTION, mCUSTOMER_ID, mCUSTOMER_PO_REF, SUBSTR(mSHIPPED_TIME,12,8), 10, mPART_ID);
COMMIT;
END IF;
END;
Have an AFTER INSERT trigger below which populates another table ONLY if a condition is met. Want to CLEAR the prior trigger commands if that condition is NOT met. What is the best way to do this ?
Other triggers on the table are not saving a record in (this table) due to my trigger.
Thanks, Steve.
DECLARE
mSHIPPED_DATE SYSADM.SHIPPER.SHIPPED_DATE%type;
mPACKLIST_ID SYSADM.SHIPPER.PACKLIST_ID%type;
mLINE_NO SYSADM.SHIPPER_LINE.LINE_NO%type;
mCUST_ORDER_ID SYSADM.SHIPPER_LINE.CUST_ORDER_ID%type;
mCUST_ORDER_LINE_NO SYSADM.SHIPPER_LINE.CUST_ORDER_LINE_NO%type;
mORDER_QTY SYSADM.CUST_ORDER_LINE.ORDER_QTY%type;
mUSER_SHIPPED_QTY SYSADM.SHIPPER_LINE.USER_SHIPPED_QTY%type;
mQTY_BO varchar2(10);
mDESCRIPTION SYSADM.PART.DESCRIPTION%type;
mCUSTOMER_ID SYSADM.CUSTOMER_ORDER.CUSTOMER_ID%type;
mCUSTOMER_PO_REF SYSADM.CUSTOMER_ORDER.CUSTOMER_PO_REF%type;
mSHIPPED_TIME varchar2(30);
mseqNo NUMBER;
mPART_ID SYSADM.CUST_ORDER_LINE.PART_ID%type;
mTOTALSHIPPEDQTY SYSADM.CUST_ORDER_LINE.TOTAL_SHIPPED_QTY%type;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT
SYSADM.SHIPPER.SHIPPED_DATE,
SYSADM.SHIPPER.PACKLIST_ID,
:OLDDATA.LINE_NO,
:OLDDATA.CUST_ORDER_ID,
:OLDDATA.CUST_ORDER_LINE_NO,
SYSADM.CUST_ORDER_LINE.ORDER_QTY,
:OLDDATA.USER_SHIPPED_QTY,
SYSADM.PART.DESCRIPTION,
SYSADM.CUSTOMER_ORDER.CUSTOMER_ID,
SYSADM.CUSTOMER_ORDER.CUSTOMER_PO_REF,
SYSADM.SHIPPER.SHIPPED_TIME,
SYSADM.CUST_ORDER_LINE.PART_ID,
SYSADM.CUST_ORDER_LINE.TOTAL_SHIPPED_QTY
INTO mSHIPPED_DATE, mPACKLIST_ID, mLINE_NO, mCUST_ORDER_ID, mCUST_ORDER_LINE_NO, mORDER_QTY, mUSER_SHIPPED_QTY, mDESCRIPTION, mCUSTOMER_ID, mCUSTOMER_PO_REF, mSHIPPED_TIME, mPART_ID, mTOTALSHIPPEDQTY
FROM SYSADM.SHIPPER, SYSADM.CUST_ORDER_LINE, SYSADM.CUSTOMER_ORDER, SYSADM.CUSTOMER, SYSADM.PART
WHERE SYSADM.SHIPPER.PACKLIST_ID= :OLDDATA.PACKLIST_ID
AND :OLDDATA.CUST_ORDER_ID = SYSADM.CUST_ORDER_LINE.CUST_ORDER_ID
AND :OLDDATA.CUST_ORDER_LINE_NO = SYSADM.CUST_ORDER_LINE.LINE_NO
AND SYSADM.CUSTOMER_ORDER.CUSTOMER_ID = SYSADM.CUSTOMER.ID
AND SYSADM.CUST_ORDER_LINE.CUST_ORDER_ID = SYSADM.CUSTOMER_ORDER.ID
AND SYSADM.CUST_ORDER_LINE.PART_ID = SYSADM.PART.ID;
IF mCUSTOMER_ID='97' AND mUSER_SHIPPED_QTY >0 THEN
SELECT TBLSDSHIPMENTS_SEQUENCE.nextval INTO mseqNo FROM dual;
INSERT INTO TBLSDSHIPMENTS (SDID, SHIPPED_DATE, PACKLIST_ID, LINE_NO, CUST_ORDER_ID, CUST_ORDER_LINE_NO, ORDER_QTY, USER_SHIPPED_QTY, QTY_BO, DESCRIPTION, CUSTOMER_ID, CUSTOMER_PO_REF, SHIPPED_TIME, SDSHIPMENTSTATUS, PART_ID)
VALUES (mseqNo, mSHIPPED_DATE, mPACKLIST_ID, mLINE_NO, mCUST_ORDER_ID, mCUST_ORDER_LINE_NO, mORDER_QTY, mUSER_SHIPPED_QTY, mORDER_QTY - (mTOTALSHIPPEDQTY + mUSER_SHIPPED_QTY), mDESCRIPTION, mCUSTOMER_ID, mCUSTOMER_PO_REF, SUBSTR(mSHIPPED_TIME,12,8), 10, mPART_ID);
COMMIT;
END IF;
END;