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

swethasivaram@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 --

swethasivaram@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...0/triggers.htm
http://download-east.oracle.com/docs...s_triggers.htm

HTH
Thomas Olszewicki
CPAS Systems Inc.

Hi,

You can all any function into your triggers. this is possible.

Thanks,


swethasivaram@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

Thanks Thomas.

Swetha


ThomasO@cpas.com wrote:
> swethasivaram@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...0/triggers.htm
> http://download-east.oracle.com/docs...s_triggers.htm
>
> HTH
> Thomas Olszewicki
> CPAS Systems Inc.