CODEWITHSUNDEEP
sqlsql-serverdatabaseweb-servicesstored-procedures
Mark
Mark

Reputation: 4883

Dynamically update "Status" column after "X" amount of time

I'm rather new to SQL Server, but I am working on an app where a record is added to a table, and is given a DateTime stamp.

I want to be able to dynamically update the Status column of this row, 1 hour after the row was added.

Is this possible without running some server side script or store procedure every couple minutes? Is there an efficient way to accomplish this?

Upvotes: 3

Views: 3276

Answers (1)

M.Ali
M.Ali

Reputation: 69554

In Sql Server you can have Time Dependant or Action Dependent code execution.

Time Dependent

Time Dependant Code execution is handled via SQL Server Agent Jobs. You can execute a stored procedure or ad-hoc T-SQL code on a certain time of the day. It can be scheduled to execute on regular basis.

Action Dependent

Action Dependent Code execution is handled via Triggers (After/Instead of Triggers). A piece of code that is executed in response to a DML action INSERT, UPDATE or DELETE.

Solution

In your case you are trying to execute code in response to an action (Insert) after a certain period of time. I dont think there is an efficient way of doing it I would rather do the following....

You can have a Column called Created of Datetime datatype in your table and set a default value of GETDATE().

Now you dont need the status column. All you need is a query/View which will check at runtime if the row was added more than an hour ago and will return it STATUS as required.

Something like.....

CREATE VIEW dbo.vw_Current_Status 
AS
SELECT * 
     , CASE WHEN  DATEDIFF(MINUTE, Created, GETDATE()) >= 60
             THEN 'OLD'
           ELSE 'New' END AS [Status]
FROM TABLE_NAME

Upvotes: 3

Related Questions