Re: Calling an SQL function from within an Oracle trigger

swethasivaram_at_gmail.com wrote:
> Hello all
>
> I have the following requirement:
>
> Before insert or update on a table tableA, I need to check if the value
> for fieldAtableA which is being inserted or updated in tableA is within
> the range specified by two values from another table tableB.
>
> I have a function which performs this check and returns me true if the
> value falls within the range or false if it doesnt.
>
> I want to have a trigger before insert or update on tableA which calls
> this function. How do I call this function from the trigger and how can
> I use the return value to either allow the insert/update or disallow
> it?
>
> Any suggestions will be greatly appreciated.
>
> Thanks
> Swetha

I am not going to bother to code an example but all you needs is a before insert row trigger. Perform a select your_func into variable from sys.dual then test the result for true or false. If true do nothing so the insert proceeds and if false raise_application_error to stop the transaction.

See the pl/sql manual for raise_application_error.

Your best source for information on how to code triggers is probably the Oracle9i Application Developer's Guide - Fundamentals Release 2 (9.2) Part Number A96590-01, Ch 15 Using Triggers, or equilivent for your version

HTH -- Mark D Powell -- Received on Tue Aug 29 2006 - 09:09:14 CDT