如何通过SQL触发器仅选取dbo.Sensors表新增/更新行供Python发送至broker

方案合理性说明 #

你想用触发器捕获增改数据的基础方向是可行的，但你纠结的两种操作方式都存在不可忽略的缺陷，不可直接使用：

• inserted表仅在触发器执行的上下文内生效，触发器运行结束后就会自动销毁，外部Python进程完全无法访问
• 临时表存在生命周期限制，会话断开或数据库重启就会丢失数据，并发访问也容易出现异常，稳定性无法保障

具体实现步骤 #

1. 搭建专属增量数据队列存储 #

创建一张物理队列表，专门用来存储待推送的增改数据，避免数据丢失：

CREATE TABLE dbo.Sensors_Incremental_Queue (
    QueueID INT IDENTITY(1,1) PRIMARY KEY,
    -- 下方字段和你现有dbo.Sensors表的字段保持一致即可，此处为示例
    SensorID INT,
    SensorValue DECIMAL(18,2),
    CollectTime DATETIME,
    -- 额外控制字段
    OperateType CHAR(1) NOT NULL, -- 标记操作类型：I=新增，U=更新
    PushStatus TINYINT NOT NULL DEFAULT 0, -- 推送状态：0=待推送，1=推送成功，2=推送失败
    CreateTime DATETIME NOT NULL DEFAULT GETDATE()
)

2. 调整触发器逻辑 #

修改你现有的触发器，仅负责把增改数据写入上面的队列表即可：

CREATE OR ALTER TRIGGER trg_Sensors_CaptureChange
ON dbo.Sensors
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    -- 写入新增数据
    INSERT INTO dbo.Sensors_Incremental_Queue (SensorID, SensorValue, CollectTime, OperateType)
    SELECT SensorID, SensorValue, CollectTime, 'I' 
    FROM inserted
    WHERE NOT EXISTS (SELECT 1 FROM deleted WHERE deleted.SensorID = inserted.SensorID)
    
    -- 写入更新数据
    INSERT INTO dbo.Sensors_Incremental_Queue (SensorID, SensorValue, CollectTime, OperateType)
    SELECT SensorID, SensorValue, CollectTime, 'U' 
    FROM inserted
    WHERE EXISTS (SELECT 1 FROM deleted WHERE deleted.SensorID = inserted.SensorID)
END

3. Python推送逻辑实现 #

• 写常驻进程或定时任务，定期查询dbo.Sensors_Incremental_Queue中PushStatus=0的待推送数据，建议每次拉取固定条数（比如100条）避免阻塞
• 数据发送到broker成功后，将对应QueueID的PushStatus更新为1；发送失败则更新为2，可单独加失败重试逻辑
• 定期清理已经推送成功超过7天的队列数据，避免表体积无限膨胀

可选优化方案 #

如果你的表数据量极大、变动频率非常高，可以直接使用SQL Server自带的*变更数据捕获（CDC）*功能，无需自行编写触发器，稳定性和性能更高，仅配置门槛略高于触发器方案。

内容的提问来源于stack exchange，提问作者Héloïse