如何在MS SQL Server创建插入触发器处理逻辑删除记录的激活与插入

针对你的场景，我们可以创建一个INSTEAD OF INSERT触发器来拦截默认的插入行为，先检查目标user_id是否已存在（哪怕是被软删除的active=0记录），再执行恢复或插入操作。这里用MERGE语句来实现是最简洁高效的，比分开写UPDATE和INSERT更可靠，还能避免竞态问题。

直接上代码，带详细注释：

ALTER TRIGGER [dbo].[PInsertOrRestore] ON [dbo].[Participants]
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON; -- 禁止返回额外的影响行数信息，避免干扰应用程序

    -- MERGE语句实现"存在则更新，不存在则插入"的逻辑
    MERGE INTO [dbo].[Participants] AS TargetTable
    USING inserted AS SourceData
        -- 匹配条件：主键user_id相同
        ON TargetTable.user_id = SourceData.user_id
    WHEN MATCHED THEN
        -- 找到已存在的记录（包括软删除的），恢复为激活状态
        UPDATE SET TargetTable.active = 1
    WHEN NOT MATCHED THEN
        -- 没有匹配到，插入新的激活记录
        INSERT (user_id, active)
        VALUES (SourceData.user_id, SourceData.active);
END

关键说明： #

• INSTEAD OF INSERT：这个触发器类型会完全替代SQL Server默认的插入操作，让我们自定义处理逻辑，正好符合你的需求。
• MERGE语句：这是SQL Server专门用于处理这类UPSERT场景的语法，一次完成匹配检查、更新、插入三个动作，比先UPDATE再判断是否插入更高效，也能避免并发情况下的重复插入问题。
• SET NOCOUNT ON：这是触发器里的最佳实践，防止触发器返回诸如"(1 row affected)"的额外信息，很多应用程序会对这类返回值敏感，可能导致错误。
• active值的灵活性：如果你的插入语句希望强制新插入的记录active=1（不管插入时传什么值），可以把INSERT部分的SourceData.active改成1，比如VALUES (SourceData.user_id, 1)，根据你的业务需求调整即可。

这个触发器和你已有的PDel软删除触发器配合，就能形成完整的逻辑：删除时标记记录为非激活，插入相同user_id时自动恢复激活状态，新用户则正常插入新记录。

内容的提问来源于stack exchange，提问作者MRM