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

Swetha,
This looks like rather simple trigger:
CREATE OR REPLACE TRIGGER TableA$BI$ROW
BEFORE INSERT ON TableA
FOR EACH ROW
BEGIN
   IF NOT MyFunction( :NEW.MyColumn ) THEN

       RAISE_APPLICATION_ERROR( -20000,'Value '||:NEW.MyColumn||' is out of range');

   END IF;
END; Please read :
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220/triggers.htm http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm

HTH
Thomas Olszewicki
CPAS Systems Inc. Received on Tue Aug 29 2006 - 09:23:20 CDT