SQL Server存储过程中DELETE语句执行无效的问题排查与解决

首先，我注意到你的存储过程里有一个严重的列顺序不匹配问题，这会导致插入到NotificationUserBulk的数据完全错乱——即使INSERT语句没有报错，数据也是不符合预期的。先把这个问题修正，再排查DELETE的问题：

1. 修正INSERT语句的列顺序错误 #

你当前INSERT的字段列表顺序是：
[NotificationId] ,[UserId] ,[IsNotify] ,[IsShow] ,[NotifyMethod] ,[NotifyDateTime] ,[ShowDateTime] ,[IsDeleted] ,[CreatedDate] ,[ModifiedDate]

但SELECT返回的列顺序是：
UserId, NotificationId, IsNotify, IsShow, NotifyMethod, NotifyDateTime, ShowDateTime, IsDeleted, CreatedDate,ModifiedDate

这意味着你把原表的UserId插入到了目标表的NotificationId字段，把原表的NotificationId插入到了目标表的UserId字段，这显然是错误的。修正后的INSERT语句应该是：

INSERT INTO [dbo].[NotificationUserBulk] 
([NotificationId] ,[UserId] ,[IsNotify] ,[IsShow] ,[NotifyMethod] ,[NotifyDateTime] ,[ShowDateTime] ,[IsDeleted] ,[CreatedDate] ,[ModifiedDate]) 
SELECT 
  NotificationId, -- 对应目标表的NotificationId
  UserId, -- 对应目标表的UserId
  IsNotify, 
  IsShow, 
  NotifyMethod, 
  NotifyDateTime, 
  ShowDateTime, 
  IsDeleted, 
  CreatedDate,
  ModifiedDate 
FROM NotificationUsers 
WHERE IsShow=1 OR IsDeleted=1

2. 排查DELETE语句未生效的原因 #

在修正列顺序后，我们来分析DELETE没生效的几种常见可能：

触发器干扰 #

如果NotificationUsers表上存在INSTEAD OF DELETE触发器，它会直接拦截DELETE操作，替换成自定义逻辑（比如什么都不做）；或者存在AFTER DELETE触发器，会把删除的数据重新插回表中。

你可以执行以下语句查看该表的所有触发器：

SELECT name, type_desc 
FROM sys.triggers 
WHERE parent_id = OBJECT_ID('dbo.NotificationUsers')

如果有相关触发器，检查触发器的逻辑是否阻止了删除操作。

权限不足 #

执行存储过程的用户（或者存储过程的定义者）可能没有NotificationUsers表的DELETE权限。SQL Server默认会静默执行无权限的语句（除非开启严格权限检查），导致DELETE看似执行但没有实际效果。

你可以用以下方式测试权限：

-- 切换到执行存储过程的用户
EXECUTE AS USER = 'YourExecutionUser';
-- 执行测试DELETE
DELETE TOP(1) FROM dbo.NotificationUsers WHERE IsShow=1 OR IsDeleted=1;
-- 查看是否报错
SELECT ERROR_MESSAGE();
-- 切换回原用户
REVERT;

如果报错提示权限不足，需要给用户或角色分配DELETE权限：

GRANT DELETE ON dbo.NotificationUsers TO YourExecutionUser;

数据在INSERT后被修改 #

在INSERT和DELETE之间，可能有其他事务或后台进程修改了NotificationUsers表中满足IsShow=1 OR IsDeleted=1的行（比如把IsShow改成0，IsDeleted改成0），导致DELETE的WHERE条件匹配不到任何数据。

解决这个问题的最佳方式是先用临时表存储要处理的数据，再基于临时表做INSERT和DELETE，避免重复使用WHERE条件带来的风险：

ALTER PROCEDURE [dbo].[SP_Insert_NotificationUserBulk] 
AS 
BEGIN 
    SET NOCOUNT ON; 

    -- 创建临时表存储要迁移的行
    CREATE TABLE #TempMigration (
        NotificationId INT, -- 请根据实际字段类型调整
        UserId INT,
        IsNotify BIT,
        IsShow BIT,
        NotifyMethod INT,
        NotifyDateTime DATETIME,
        ShowDateTime DATETIME,
        IsDeleted BIT,
        CreatedDate DATETIME,
        ModifiedDate DATETIME
    )

    -- 把要处理的数据存入临时表
    INSERT INTO #TempMigration
    SELECT 
      NotificationId, 
      UserId, 
      IsNotify, 
      IsShow, 
      NotifyMethod, 
      NotifyDateTime, 
      ShowDateTime, 
      IsDeleted, 
      CreatedDate,
      ModifiedDate 
    FROM NotificationUsers 
    WHERE IsShow=1 OR IsDeleted=1

    -- 从临时表插入目标表
    INSERT INTO [dbo].[NotificationUserBulk] 
    ([NotificationId] ,[UserId] ,[IsNotify] ,[IsShow] ,[NotifyMethod] ,[NotifyDateTime] ,[ShowDateTime] ,[IsDeleted] ,[CreatedDate] ,[ModifiedDate]) 
    SELECT * FROM #TempMigration

    -- 基于临时表的主键删除原表数据（假设NotificationId+UserId是联合主键）
    DELETE nu
    FROM dbo.NotificationUsers nu
    JOIN #TempMigration tm 
      ON nu.NotificationId = tm.NotificationId 
      AND nu.UserId = tm.UserId

    -- 清理临时表
    DROP TABLE #TempMigration
RETURN 
END

事务隔离级别或并发问题 #

如果你的数据库使用了READ COMMITTED SNAPSHOT或SNAPSHOT隔离级别，DELETE语句可能读取的是事务开始时的快照数据，而此时原表的数据已经被其他事务修改。

解决这个问题的方式是把INSERT和DELETE放在同一个事务中，确保操作的原子性，同时锁定相关数据：

ALTER PROCEDURE [dbo].[SP_Insert_NotificationUserBulk] 
AS 
BEGIN 
    SET NOCOUNT ON; 

    BEGIN TRANSACTION;
    BEGIN TRY
        -- 修正后的INSERT语句
        INSERT INTO [dbo].[NotificationUserBulk] 
        ([NotificationId] ,[UserId] ,[IsNotify] ,[IsShow] ,[NotifyMethod] ,[NotifyDateTime] ,[ShowDateTime] ,[IsDeleted] ,[CreatedDate] ,[ModifiedDate]) 
        SELECT 
          NotificationId, 
          UserId, 
          IsNotify, 
          IsShow, 
          NotifyMethod, 
          NotifyDateTime, 
          ShowDateTime, 
          IsDeleted, 
          CreatedDate,
          ModifiedDate 
        FROM NotificationUsers 
        WHERE IsShow=1 OR IsDeleted=1

        DELETE FROM dbo.NotificationUsers 
        WHERE IsShow=1 OR IsDeleted=1

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        -- 抛出错误信息方便排查
        THROW;
    END CATCH
RETURN 
END

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