Re: Calling an SQL function from within an Oracle trigger

Thanks Thomas.

Swetha

ThomasO_at_cpas.com wrote:
> 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 Sat Sep 02 2006 - 01:46:39 CDT