Trouble with Trigger raising "no data found"

From: Robert <fibbert_at_yahoo.com>
Date: 25 Sep 2002 08:11:28 -0700
Message-ID: <4801dfd7.0209250711.247d122d_at_posting.google.com>


Hi Everyone,

I am having a very frustrating trigger issue: I have written a trigger which calls a separately defined function (text of the function is below). This function checks to see if there is an "override" price for a part from a specific vendor - and if not, returns the default price.

What is happening is that the Function raises an ORA-01403 no data found error after the SELECT VENDOR_PART_ID FROM VENDOR_PART ... in the function below. It is not getting to the IF SQL%FOUND THEN line.

Now - I have no EXCEPTION section in either the funciton or the trigger code and I'm trying to use cursor attributes to detect the no data condition - but it's not working. Could this be a bug? Is this as designed?

Thanks

Function:

CREATE OR REPLACE FUNCTION
  TK_CORRECT_UNIT_PRICE
  (
    sVendorID IN VENDOR.ID%TYPE,

		sPartID IN PART.ID%TYPE,
		nQty IN PURC_ORDER_LINE.ORDER_QTY%TYPE
  )
    RETURN NUMBER
  IS
  /* Variable declaration goes here */
    nPrice VENDOR_QUOTE.DEFAULT_UNIT_PRICE%TYPE := 0;     rowVendorQuote VENDOR_QUOTE%ROWTYPE;

                sVendorPartID VENDOR_QUOTE.VENDOR_PART_ID%TYPE := '';   BEGIN
  /* Methods and procedures go here */

		SELECT VENDOR_PART_ID INTO sVendorPartID 
		FROM VENDOR_PART
		WHERE PART_ID = sPartID AND
		VENDOR_ID = sVendorID;

> ERROR OCCURS HERE                                  if SQL%FOUND THEN         

  	  SELECT *
			  INTO
			    rowVendorQuote
			  FROM
				VENDOR_QUOTE
			  WHERE
			    VENDOR_ID = sVendorID AND
				VENDOR_PART_ID = sVendorPartID;
				
				
				
      if SQL%NOTFOUND THEN -- We did not find a row matching our
criteria
		    SELECT
			  (NVL(UNIT_MATERIAL_COST,0) + NVL(UNIT_SERVICE_COST,0))
				INTO
			 	 nPrice
				FROM
				  Part
				WHERE
				  ID = sPartID;

				if SQL%NOTFOUND THEN
				   raise_application_error(-20999, 'VMFG-20504 Errors occurred in
method TK_CORRECT_UNIT_PRICE');
				   nPrice := 0;
				End if;

		  Else
		  --We did find a matching row in VENDOR_QUOTE,  Now we need to find
out what price is
		  --Correct for the given materials


		    if (rowVendorQuote.QTY_BREAK_5 < nQty) and

(rowVendorQuote.QTY_BREAK_5 IS NOT NULL) then
nPrice := rowVendorQuote.UNIT_PRICE_5; elsif (rowVendorQuote.QTY_BREAK_4 < nQty) and
(rowVendorQuote.QTY_BREAK_4 IS NOT NULL) then
nPrice := rowVendorQuote.UNIT_PRICE_4; elsif (rowVendorQuote.QTY_BREAK_3 < nQty) and
(rowVendorQuote.QTY_BREAK_3 IS NOT NULL) then
nPrice := rowVendorQuote.UNIT_PRICE_3; elsif (rowVendorQuote.QTY_BREAK_2 < nQty) and
(rowVendorQuote.QTY_BREAK_2 IS NOT NULL) then
nPrice := rowVendorQuote.UNIT_PRICE_2; elsif (rowVendorQuote.QTY_BREAK_1 < nQty) and

(rowVendorQuote.QTY_BREAK_1 IS NOT NULL) then
nPrice := rowVendorQuote.UNIT_PRICE_1; else nPrice := rowVendorQuote.DEFAULT_UNIT_PRICE; end if; End if; Else SELECT (NVL(UNIT_MATERIAL_COST,0) + NVL(UNIT_SERVICE_COST,0)) INTO nPrice FROM Part WHERE ID = sPartID; if SQL%NOTFOUND THEN raise_application_error(-20999, 'VMFG-20504 Errors occurred in method TK_CORRECT_UNIT_PRICE'); nPrice := 0; End if; End If;

  RETURN(nPrice);

        END;
/ Received on Wed Sep 25 2002 - 17:11:28 CEST

Original text of this message