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_DATE, CURRENT_TIMESTAMP, DBTIMEZONE, LOCALTIMESTAMP,  SESSIONTIMEZONE,   SYSDATE, SYSTIMESTAMP, UID,  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 CURRVAL, NEXTVAL, LEVEL, 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;

add to del.icio.us saved by 0 users

A Glass Jar And Two Cups Of Tea

 Glass Jar And Two Cups Of Tea

A life reforming Story

A time comes in life when we desire to do everything very fast ,to get everything as soon as possible, and then twenty-four hours a day seems less to have that, at that time we should remember this life reforming story, "A glass jar and two cups of tea".
A professor of philosophy came in the classroom and told the students that he is going to teach an important lesson of life. 
He brought along a big glass jar and kept it on the table and then he began putting table tennis balls in the jar until the jar was full enough, not to take any more balls.
He asked the students, “Is the jar completely filled?”
Students shouted, “Yes.”
Then Professor started putting small stone fillings in the jar. He shook the jar and a good amount of stone filling were adjusted in the jar.
The Professor asked again, “Is the jar completely filled now?”
Students shouted once again, ”Yes.”
 Then the Professor started pouring sand slowly in that jar.
Sand made its way into the jar where it was possible.
 Now students laughed at their innocence.
 Professor asked again “Now this jar is completely filled, isn’t it?”
Students said,” Yes, Now it is full.”
Then professor took two cup of tea and poured into the jar.The tea made its place in the jar and was soaked by the sand.
Now Professor started explaining everything in a serious voice.
Consider this glass jar as your life. The most important things are table tennis balls i.e. God, family, kids, friends, Health and hobbies.
Small stone fillings symbolises your job, car, house etc.
 Sand means some more small useless things like estrangement, misunderstandings, quarrels etc.
Now if you fill the glass jar with sand first then there will be no space left for balls and stone fillings. Or if you put stone fillings first then there will be no room for table tennis ball, but still that could hold sands.
Same philosophy applies to life.
If you are going behind those little things and destroying you energy on them then you will not have much time to spend on the necessary and important things.
What is important for the happiness of soul? First decide that. Play with your children, put water in the garden, and go for morning walk with wife.Throw out all useless stuff from home; go for medical check-up regularly.Worry about table tennis balls first, that is most important. First decide what is important? Rest all is send only.
Students were listening attentively.
 Suddenly a student asked, “Sir, but you have not told anything about those two cups of tea." What is that meant for?
Professor smiled and said,” I was expecting this question for a long time.”
The answer is, “no matter how busy, complete and satisfied your life seems, but there must be place for sharing two cups of tea with your special friend.
So consider this story seriously and try to implement it in your life to make your life happy and peaceful.

add to del.icio.us saved by 0 users