Expiration date field constraint. [message #21398] |
Thu, 01 August 2002 05:27 |
Gautham Sunkara
Messages: 2 Registered: July 2002
|
Junior Member |
|
|
Hi
I had to have a contraint of the expiration date that
If the expiration date is older than the present date I should not retrieve that record.
I used the following statement for implement that constraint
TO_CHAR(ppp.expiration_date, 'MM/DD/YYYY') > TO_CHAR(SYSDATE, 'MM-DD-YYYY')
The problem is I am still getting records retrieved which violate the above constraint.
Can anyone sort this problem or is there any better way to implement it.
Thanks,
Gautham.
|
|
|
|
Re: Expiration date field constraint. [message #21408 is a reply to message #21398] |
Thu, 01 August 2002 10:13 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
You can't reference sysdate in a check constraint. Try a trigger.
create table date_tab (date_col date);
Table created
CREATE OR REPLACE TRIGGER date_tab_biu
BEFORE INSERT OR UPDATE
ON date_tab
FOR EACH ROW
BEGIN
IF :NEW.date_col >= SYSDATE
THEN
raise_application_error (-20501, 'ERROR: DATE_COL must be < sysdate');
END IF;
END;
/
Trigger created
insert into date_tab values(sysdate - 1)
1 row inserted
insert into date_tab values(sysdate);
ORA-20501: ERROR: DATE_COL must be < sysdate
ORA-06512: at "SCOTT.DATE_TAB_BIU", line 4
ORA-04088: error during execution of trigger 'SCOTT.DATE_TAB_BIU'
insert into date_tab values(sysdate + 1);
ORA-20501: ERROR: DATE_COL must be < sysdate
ORA-06512: at "SCOTT.DATE_TAB_BIU", line 4
ORA-04088: error during execution of trigger 'SCOTT.DATE_TAB_BIU'
|
|
|