Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SYSDATE problem

Re: SYSDATE problem

From: Joel R. Kallman <jkallman_at_us.oracle.com>
Date: Fri, 20 Nov 1998 15:09:20 GMT
Message-ID: <3656827f.11342309@newshost.us.oracle.com>


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



20-NOV-98 SQL> insert into mydates(date1, date2)
values('19-NOV-98','19-NOV-98');
insert into mydates(date1, date2) values('19-NOV-98','19-NOV-98')

            *
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




The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Received on Fri Nov 20 1998 - 09:09:20 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US