Re: What's wrong with this picture?

mark m <mockm_at_my-deja.com> wrote in message news:84b9h2$hcu$1_at_nnrp1.deja.com...
> I have a function...
>
> create or replace function current_date
> return date as
>
> the_date date;
>
> begin
>
> select sysdate
> into the_date
> from sys.dual;
>
> return (the_date);
>
> end;
>
> ...which when used in this constraint...
>
> alter table fac_contracts
> add(constraint fcon_current_ck
> check (start_date < current_date and (end_date > current_date or
> end_date is null)));
>
> ...produces this error...
>
> SQLWKS> alter table fac_contracts
> 2> add(constraint fcon_current_ck
> 3> check (start_date < current_date and (end_date >
> current_date or end_date is null)));
> check (start_date < current_date and (end_date > current_date or
> end_date is null)))
> *
> ORA-00904: invalid column name
>
> What am I missing?

You can not use user-defined functions in check constraints, since the definitions of user-defined functions may be changed. Also, you can not use SYSDATE in check constraints. Your function is redundant, use SYSDATE instead, if in a local database.

You should use triggers to perform your requirement. Received on Thu Dec 30 1999 - 09:18:31 CST