Trouble with Trigger raising "no data found"
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
sVendorPartID VENDOR_QUOTE.VENDOR_PART_ID%TYPE := '';
BEGIN
> ERROR OCCURS HERE
if SQL%FOUND THEN
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;
/* Methods and procedures go here */
SELECT VENDOR_PART_ID INTO sVendorPartID
FROM VENDOR_PART
WHERE PART_ID = sPartID AND
VENDOR_ID = sVendorID;
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