检查数据库并每分钟执行操作的最佳方式

Question

我有一个电子商务应用程序。我有一个Item实体，每当该项目的结束日期时间等于当前时间时，该项目的状态就会发生变化(我还需要执行其他SQL操作，如向表中插入行(

基本上，我想执行一个SQL操作，该操作每分钟检查数据库并更改实体。

我对如何实现这个有一些想法：

• 在我的linux服务器中安排一个每分钟检查数据库的作业
• 使用sp_executesql(Transact-SQL(或DBMS计划程序
• 在我的Java后端运行一个线程来检查数据库并执行操作

我对此非常陌生，所以我不知道如何实现它。考虑到可伸缩性性能的最有效的实现是什么？

其他信息：数据库是SQL Server，服务器是Linux，后端是Java Spring Boot。

Answer 1

如果您需要在插入或更新后运行脚本，您可以将所有复杂的逻辑(例如，在其他表中插入行、更新状态列等(合并到触发器中：

下面是一个示例表模式：

CREATE TABLE t1 (id INT IDENTITY(1,1), start_time DATETIME, end_time DATETIME, 
status VARCHAR(25))

以及该表的示例插入/更新触发器：

CREATE TRIGGER u_t1 
ON t1
AFTER INSERT,UPDATE
AS
BEGIN
UPDATE t1
SET status = CASE WHEN inserted.end_time = inserted.start_time 
THEN 'same' ELSE 'different' END 
FROM t1 
INNER JOIN inserted ON t1.id = inserted.id
-- do anything else you want!
-- e.g. 
-- INSERT INTO t2 (id, status) SELECT id, status FROM inserted
END
GO

插入几个测试记录：

INSERT INTO t1 (start_time, end_time) 
VALUES 
(GETDATE(), GETDATE() - 1), -- different
(GETDATE(), GETDATE())      -- same

插入后查询表：

SELECT * FROM t1

确保状态计算正确：

id  start_time              end_time                status
1   2018-07-17 02:53:24.577 2018-07-16 02:53:24.577 different
2   2018-07-17 02:53:24.577 2018-07-17 02:53:24.577 same

如果您的唯一目标是根据表中的其他值更新状态列，那么计算列是最简单的方法；你只需要提供公式：

create table t1 (id int identity(1,1), start_time datetime, end_time datetime, 
status as 
case 
when start_time is null then 'start null' 
when end_time is null then 'end null' 
when start_time < end_time then 'start less' 
when end_time < start_time then 'end less' 
when start_time = end_time then 'same' 
else 'what?' 
end
)