Re: function in check constraint???

Thomas Kyte wrote:

> > -----Original Message-----
> > From: Randall J. Parr [mailto:rparr_at_temporal.com]
> > Sent: Friday, March 05, 1999 1:11 PM
> > To: tkyte_at_us.oracle.com
> > Subject: Re: function in check constraint???
> >
> >
> > Thomas Kyte wrote:
> >
> > > >Is it possible to use/call a function in a check constraint???
> > > >
> > >
> > > no it is not.
> >
> > Bummer. I had secondary reasons for wanting to use a function.
> >
> > I was hoping to define data validation "functions" could be used
> > in constraints AND
> > called to validate data during input (especially Java/JFC data
> > input). If I can't
> > call functions from contraints I have to either use all triggers
> > or define the
> > "constraint" two (or more) times.
>
> I think you can still achive what you want. Your trigger would look like:
>
> create or replace trigger xxxxx
> before insert or update
> for each row
> as
> if ( perPersonValidate( :new.x ) = 0 ) )
> then
> raise_application_error( -20001, 'Error!!!' );
> end if;
>
> if ( your_other_function( :new.the_other_column ) )
> ......
> end;
>
> and you can still call perpersonvalidate from java or whatever..
>

To clarify...

You're saying write a validation function and then use a trigger (which calls the validation function) instead of a constraint.

I understand the idea, and, I believe, the consequences. Eg. triggers only apply to data entered after they're enabled and thus would have to be enabled during loads whereas constraints can be disabled duing load and enabled afterwards and will check the loaded data.

What is the performance trade-off. I was afraid the trigger approach would be considerably less efficient than the constraint approach. Especially given that, in some tables, I have 6, 10, or more of these type of validations to perform.

ALSO In looking at this particular problem, I realized I have also been checking (in a different constraint) for lowercase when in fact I need to FORCE lower case. Do I have to use a trigger to force to lower case?

Thanks
R.Parr

P.S. Let me know if I'm imposing with these additional questions. Received on Fri Mar 05 1999 - 13:06:47 CST