TRIGGER - HOW TO CLEAR ?

[saustin]

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;

 

[LKBrwnDBA]

1) You cannot COMMIT in a trigger.

2) Please clarify your requirements and post COMPLETE code.

Thanks,




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[saustin]

Thanks for the reply. Took the commit out but have the same result. Requirement is to save data from this and other table in another table if the following conditions are met
....

..... IF mCUSTOMER_ID='97' AND mUSER_SHIPPED_QTY >0

Again, how do you CLEAR the trigger commands if this condition is NOT met.

 

[LKBrwnDBA]

Don't understand what you mean by "CLEAR the trigger commands"?

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[saustin]

Want none of the commands (lines) to effect the table if that condition is not met. For some reason now even though the code above seems straight forward another trigger on the table has a problem with my code and will not save a record in the table even though this is an after insert trigger. Was hoping for some REVERT, or CLEAR command that could be executed if the insert condition was not met so i could back out gracefully.

 

[LKBrwnDBA]

Do you mean ROLLBACK?

If you need to rollback the transaction if the condition is not met, then just raise an exception in the trigger:

...
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);
ELSE
   RAISE_APPLICATION_ERROR(-20001, '!ERROR, mCUSTOMER_ID='97' AND mUSER_SHIPPED_QTY >0 NOT MET.');
END IF;
...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[saustin]

Rollback will trouble the enduser with an error message they need to clear.

In the meantime have found a way around this by copying values to another temp table with no triggers on it.

Many thanks, Steve.