Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SYSDATE problem
On Thu, 19 Nov 1998 16:56:07 -0800, Teresa Mah <tmah_at_home.com> wrote:
>Hi,
>
>I'm trying to create a table, with one of the date attributes having the
>constraint that the date has to be greater than the current date:
>
>I've defined the table as follows:
>
>SQL> create table mydates (
> 2 date1 DATE not null primary key,
> 3 date2 DATE CHECK (date2 > SYSDATE),
> 4 date3 DATE,
> 5 date4 DATE);
>date2 DATE CHECK (date2 > SYSDATE),
> *
This type of constraint doesn't semantically make sense. When would you want this integrity constraint enforced? Only when you INSERT a row? If I entered a row yesterday, and then I update a column in that row, do I want this integrity constraint enforced.
You can implement this easily using a trigger, as in:
CREATE OR REPLACE TRIGGER tr_bi_mydates BEFORE INSERT ON mydates FOR EACH ROW DECLARE invalid_date EXCEPTION; BEGIN IF (:new.date2 <= SYSDATE) THEN RAISE invalid_date; END IF; EXCEPTION WHEN invalid_date THEN raise_application_error(-20500, 'Invalid date'); END;
SQL> select sysdate from dual;
SYSDATE
*
ERROR at line 1:
ORA-20500: Invalid date ORA-06512: at "SCOTT.TR_BI_MYDATES", line 9 ORA-04088: error during execution of trigger 'SCOTT.TR_BI_MYDATES'
SQL> insert into mydates(date1, date2)
values('25-NOV-98','25-NOV-98');
1 row created.
SQL>
>ERROR at line 3:
>ORA-02436: date or system variable wrongly specified in CHECK
>constraint
>Can anyone tell me what's wrong?
>
>Thank you in advance.
>
>Teresa
Thanks!
Joel
Joel R. Kallman Oracle Government, Education, & Health
Columbus, OH http://govt.us.oracle.com jkallman@us.oracle.com http://www.oracle.com