Infolinks

Friday 13 July 2012

CHECK CONSTRAINTS ON SYSDATE IN ORACLE

CHECK CONSTRAINTS ON SYSDATE IN ORACLE

RESTRICTIONS ON CHECK CONSTRAINTS

I.We 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.
II.The condition of a check constraint can refer to any column in the table, but it cannot refer to columns of other        tables.
III.Conditions of check constraints cannot contain the following constructs:


  1. Subqueries and scalar subquery expressions
  2. Calls to the functions that are not deterministic (CURRENT_DATECURRENT_TIMESTAMPDBTIMEZONELOCALTIMESTAMP SESSIONTIMEZONE,   SYSDATESYSTIMESTAMPUID,  USER, and USERENV)
  3. Calls to user-defined functions
  4. Dereferencing of REF columns (for example, using the DEREF function)
  5. Nested table columns or attributes
  6. The pseudocolumns CURRVALNEXTVALLEVEL, or ROWNUM
  7. Date constants that are not fully specified
So from the above detail its quite clear that we can not write any comparison with SYSDATE in check constrains..

Now imagine a situation that we are having one table and in that table we want some validation. The requirement is that we are taking Date Of Birth from the user and we want that only that user's details can reside inside our table who is older then 18 years else his insert should not take place.

So for this validation we have to write one trigger to take care of this situation.




/*This trigger will fire on every insert on table name TABLE_NAME and check that the date inserted in DOB_COLUMN_NAME column is greater than the difference of SYSDATE and inserted value by 18 and accordingly either it will allow the data to go inside the table or abort the transaction. :NEW and :OLD are co-relation identifiers used to check for the value inserted, updated or deleted. It will work only in triggers*/


CREATE OR REPLACE TRIGGER PROJECT_NAME_AGE_CHECK_TRIGGER
 AFTER INSERT AFTER UPDATE ON TABLE_NAME
 FOR EACH ROW
 DECLARE
 V_AGECHECK EXCEPTION;
 BEGIN
 IF MONTHS_BETWEEN(SYSDATE,:NEW.DOB_COLUMN_NAME)/12 < 18 THEN
 RAISE V_AGECHECK;
 END IF;
 EXCEPTION
 WHEN V_AGECHECK THEN
 RAISE_APPLICATION_ERROR(-20001,'YOU ARE MINOR') ;
 END;

No comments:

Post a Comment