how to check the dob is less than the curent date

[muthulk2]

i want a create a table with DOB column and i want add a constraint to check the DOB less than the current date using dual table

[Luuk]

for those who did not know:
the definition of DOB is here

CREATE TRIGGER <triggername>
BEFORE INSERT OT UPDATE OF DOB ON <tablename>
FOR EACH ROW
WHEN (new.DOB < CURRENT_DATE)
BEGIN
  raise_application_error( -20001,'DOB must be before today');
END;
 

above answer is not tested.

resources:
1) http://www.toadworld.com/platforms/o...-examples.aspx
2) http://stackoverflow.com/questions/9...d-update-queri

[debasisdas]

Try using this.

CREATE TABLE EMP
(EMP_ID   NUMBER(10),
 ENTRY_DATE DATE DEFAULT SYSDATE NOT NULL,
 EMP_DOB  DATE,
CONSTRAINT EMP_DOB_NON_FUTURE CHECK (EMP_DOB <= ENTRY_DATE)
);

Following is the list of Restrictions on Check Constraints in Oracle:

You cannot specify a check constraint for a view. However, you can define the view using the WITH CHECK OPTION clause, which is equivalent to specifying a check constraint for the view.

The condition of a check constraint can refer to any column in the table, but it cannot refer to columns of other tables.

Conditions of check constraints cannot contain the following constructs:

Subqueries and scalar subquery expressions

Calls to the functions that are not deterministic (CURRENT_DATE, CURRENT_TIMESTAMP, DBTIMEZONE, LOCALTIMESTAMP, SESSIONTIMEZONE, SYSDATE, SYSTIMESTAMP, UID, USER, and USERENV)

Calls to user-defined functions

Dereferencing of REF columns (for example, using the DEREF function)

Nested table columns or attributes

The pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM

Date constants that are not fully specified