Home » SQL & PL/SQL » SQL & PL/SQL » how to restrict while inserting date information (sql (oracle 9i))
how to restrict while inserting date information [message #428536] Wed, 28 October 2009 12:40 Go to next message
sivaora
Messages: 119
Registered: October 2009
Location: Hyderabad
Senior Member
hi all,

I created one table like this
>create table temp(dt timestamp);
table created.

then i am inserting one row,

>insert into temp values('1-jan-2009 10:30:40 am');
1 row created.

now my requirement is i have to restrict the input date and time must be less than current server time.
i.e if the current date & time is '28-oct-2009 11:10:30 pm'
then inserting row must be less than this like '28-oct-2009 11:09:00 pm'.

Using check constraint i am not getting this. if any one knows please reply me, and the solution must be in sql only.
Re: how to restrict while inserting date information [message #428537 is a reply to message #428536] Wed, 28 October 2009 12:53 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
I do not believe you can make the check constraint "dynamic" like that. It has to use a static value.
You can always make a trigger.
Re: how to restrict while inserting date information [message #428538 is a reply to message #428536] Wed, 28 October 2009 12:54 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Using check constraint i am not getting this.
If you were willing & able to Read The Fine Manual
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses002.htm#sthref2914

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)


>if any one knows please reply me, and the solution must be in sql only.

Good Luck!

[Updated on: Wed, 28 October 2009 12:58]

Report message to a moderator

Re: how to restrict while inserting date information [message #428625 is a reply to message #428536] Thu, 29 October 2009 03:52 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
As its already been said, you can not do it with constraint.
As Joy_division Sir said, you can not make the check constraint "dynamic" like that. It has to use a static value.



1.Get the server date time store it in a variable
2. Store the paasing date value in a variable
3. Compare the two
4. If it satisfies your requirement then insert the row
else null;

[Updated on: Thu, 29 October 2009 03:52]

Report message to a moderator

Re: how to restrict while inserting date information [message #428629 is a reply to message #428625] Thu, 29 October 2009 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand your point; it can be done with a trigger.
This is one of the main purposes of triggers to check input data.

Regards
Michel
Re: how to restrict while inserting date information [message #428639 is a reply to message #428629] Thu, 29 October 2009 04:35 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Sir,
I put my points by using Stored procedure.

Re: how to restrict while inserting date information [message #428640 is a reply to message #428639] Thu, 29 October 2009 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is what I was thinking; as I said it is better to use a trigger in this case.

Regards
Michel
Re: how to restrict while inserting date information [message #428644 is a reply to message #428640] Thu, 29 October 2009 04:43 Go to previous message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Yes, I agree it would be better to put constraint using trigger
then you dont have to put the constraint in other procedure where
you are refering the same table for inserting data.
Previous Topic: Invalid identifier when update
Next Topic: Can this be done with a REGEXP
Goto Forum:
  


Current Time: Sat Dec 10 20:37:09 CST 2016

Total time taken to generate the page: 0.17315 seconds