Updating a record in the table at a given time in sqlserver

Question

1.00/5 (1 vote)

See more:

Dear All,

Sorry preciously my question was wrong...

Can anyone please guide me with some solution. Right now I am adding som infomation in a table, lets say as Sampletb. I have some column like Time,Status in Sampletb.

My requirement is if I enter any record in the Sampletb with a date (for ex:27-dec-2013 23:00) in the time column.

On this particular date and time,status of the particular record should be changed to 1....

could this be possible...

Please help me with this...

Posted 25-Dec-13 20:57pm

Rakesh Aytha

Updated 26-Dec-13 0:44am

v3

Add a Solution

5 solutions

Solution 5

I have done this by generating sqljobs whenever any records are inserted in to the table.

For Generating the Jobs dynamically we have some store procedures like sp_add_job by default.

I created a SP in which I am calling SP_ADD_JOB by giving the date and time as parameters..

so whenever I insert any date in the table it creates a job dynamically and takes the date i insert as parameters and triggers at that time...

Hope it helps some one who is facing the same issue.

Posted 3-Jan-14 9:45am

Rakesh Aytha

Solution 3

If the status is set by the date, why not use a view to return the values and generate that column dynamically.

Posted 25-Dec-13 21:39pm

Christian Graus

Comments

[no name] 26-Dec-13 5:50am

Can you explain me in brief...Actually I am Inserting one recoed in a table with a particular date ..on that date Inserted the corresponding record status should be updated..

Christian Graus 26-Dec-13 18:05pm

If the status depends solely on the date, then you don't need to store a status, you can create a view using a CASE statement to create the status on the fly, and thus to always make it up to date without requiring a trigger. It may be more efficient to store the status, your milage may vary. It's just another option.

Solution 4

I have done this by generating dynamic sql jobs in a store procedure whenever any records gets submitted..

Posted 3-Jan-14 9:39am

Rakesh Aytha

Comments

CHill60 3-Jan-14 15:43pm

Really? Either a trigger to update the database, or define the status via a view would have been a better approach - see the other solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

viprat · Accepted Answer · 2013-12-25T21:31:00

Solution 2

Hi,

you can user trigger for that. See the below link it will show you how to use it.

http://www.sqlusa.com/bestpractices/triggerformissingdata/[^]

http://technet.microsoft.com/en-us/library/ms189799.aspx[^]

Triggers -- SQL Server[^]

Posted 25-Dec-13 21:31pm

viprat

Comments

[no name] 26-Dec-13 5:50am

Thanks for the suggestion..I am working on it...

Tom Marvolo Riddle · Accepted Answer · 2013-12-25T21:02:00

Solution 1

Use Triggers

Check this link:
Triggers -- SQL Server[^]

Posted 25-Dec-13 21:02pm

Tom Marvolo Riddle

Comments

[no name] 26-Dec-13 5:50am

Thanks for the suggestion.