动态更新“状态”消息。 “ X”之后的列多少时间 [英] Dynamically update "Status" column after "X" amount of time

问题描述

我对SQL Server还是很陌生，但是我正在开发一个应用程序，该应用程序将记录添加到表中，并被赋予 DateTime 标记。

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.

我希望能够在添加行1小时后动态更新此行的状态列

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?

推荐答案

在Sql Server中，您可以具有与时间相关的或取决于动作的代码执行。

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

时间依赖通过SQL Server代理作业处理代码执行。您可以在一天的特定时间执行存储过程或临时的T-SQL代码。可以安排它定期执行。

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.

依赖于动作代码执行通过触发器（在触发器之后/代替触发器）进行处理。响应DML操作INSERT，UPDATE或DELETE而执行的一段代码。

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.

在您的情况下，您尝试执行代码以响应操作（插入）一定的时间。我不认为有一种有效的方法，我宁愿执行以下操作。...

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....

您可以有一个名为Created of 的列表中的Datetime 数据类型，并设置默认值为 GETDATE（）。

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.

类似。 ...

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

这篇关于动态更新“状态”消息。 “ X”之后的列多少时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！