How to give restriction on Uploading Date? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to give restriction on Uploading Date?

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2009-05-05 : 15:05:38

Sir,
I have a table called project, in this table i have 2 columns date(datetime) and project(text). Now I want to give restriction in such a way that if I give a certain date to upload the project, then after that date is over, noone can further input anything in the project column.
Hope I can make you understand my problem.
I don't know how to do this, please help me!

Daipayan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-05-05 : 15:16:23

Your question is not clear to me. Please post a data sample so that we can better understand it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

daipayan
Posting Yak Master

181 Posts

Posted - 2009-05-05 : 15:33:03

Ok!!

Table: Project
--------------------
  Date    | Project
--------------------
05/05/2009|  ABCD
05/05/2009|
--------------------

See,in this table, on 05/05/2009, a project ABCD(say) is being uploaded. But on 2nd 05/05/2009, if someone try to upload a project on 06/05/2009, he/she can't as today's date is 06/05/2009 and restriction is given that he/she can upload his/her project within the time limit i.e. by 05/05/2009, not after that.

Hope, now I can make you understand my problem!

Daipayan

daipayan
Posting Yak Master

181 Posts

Posted - 2009-05-09 : 11:15:46

Sir,
I tried this following trigger:

CREATE TRIGGER Main_TABLE_TRG
ON MAIN_TABLE
FOR INSERT
AS
BEGIN

declare
@date datetime,
@proj varchar(100)

select @date1 = date,@proj = project
FROM INSERTED

If not exists (select 1 from second_table
where proj = @proj
and getdate()/*or @date1*/ between valid_from and valid_to)
begin
RAISERROR ('Project updating/ inserting expiries', 16, 1)
return
end

END

For this trigger, I created two table:

MAIN_TABLE
(
date datetime,
project varchar(100)
)
second_table
(
proj varchar(100),
valid_from datetime,
valid_to datetime
)

BUT, In second_table, when I inserted following data:

----------------------------
proj | valid_from | valid_to
----------------------------
ABCD   1/7/2009     7/7/2009
----------------------------

and in MAIN_TABLE:
--------------------
date     | project
--------------------
5/7/2009    ABCD
7/7/2009    ABCD
8/7/2009    ABCD
--------------------

MAIN_TABLE taking the project on 8/7/2009 without giving the alert.
and am not understanding where is the problem in TRIGGER!

Please help!!

Daipayan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-09 : 12:29:10

[code]
CREATE TRIGGER Main_TABLE_TRG
ON MAIN_TABLE
FOR INSERT
AS
BEGIN
If exists (select 1 from second_table s
JOIN INSERTED i
ON i.project= s.proj
AND (i.date<s.valid_from
OR i.date > s.valid_to)
begin
RAISERROR ('Date entered is outside the valid range for the project', 16, 1)
return
end
END
[/code]

daipayan
Posting Yak Master

181 Posts

Posted - 2009-05-10 : 11:33:57

Sir,

Everything is fine, but am getting syntax error in the word 'begin' in the following part:

begin
RAISERROR ('Date entered is outside the valid range for the project', 16, 1)
return

Daipayan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-10 : 12:58:09

i missed a braces


CREATE TRIGGER Main_TABLE_TRG
ON MAIN_TABLE
FOR INSERT
AS
BEGIN
If exists (select 1 from second_table s
JOIN INSERTED i
ON i.project= s.proj
AND (i.date<s.valid_from
OR i.date > s.valid_to))
begin
RAISERROR ('Date entered is outside the valid range for the project', 16, 1)
return
end
END

daipayan
Posting Yak Master

181 Posts

Posted - 2009-05-11 : 11:59:07

Thanks a lot Sir.

Daipayan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-11 : 12:55:09

welcome

daipayan
Posting Yak Master

181 Posts

Posted - 2009-05-16 : 10:23:29

quote:
Originally posted by visakh16

welcome

Hello Sir,
Sorry again disturbing you on this topic.
I am facing a problem, I had included two more columns in MAIN_Table i.e. project_details (image).
Now, whenever am inserting data, beyond the valid range, the error message is showing but also accepting the data two times.

What can be the problem??

Daipayan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-16 : 14:34:29

sorry didnt get you. accepting data two times? do you have any other triggers in table?

daipayan
Posting Yak Master

181 Posts

Posted - 2009-05-19 : 05:41:55

quote:
Originally posted by visakh16

sorry didnt get you. accepting data two times? do you have any other triggers in table?

No, sir there is only one trigger only.
Two times means,if am inserting following data:

In second_table, when I am defining the validation range:
-----------------------------
proj | valid_from | valid_to
-----------------------------
ABCD   1/7/2009     4/7/2009
-----------------------------
then in MAIN_TABLE the data is being inserted in following way:
-------------------------------------
date     | project | project_details
-------------------------------------
5/7/2009    ABCD        <binary>
5/7/2009    ABCD        <binary>
-------------------------------------
and also showing the following error message:
Date entered is outside the valid range for the project

Daipayan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources