如何创建T-SQL INSTEAD OF DELETE触发器打印行ID而不实际删除行

可以实现该需求，使用SQL Server提供的INSTEAD OF DELETE触发器即可，这类触发器会替代默认的删除操作执行自定义逻辑，不会实际删除表中数据，同时可以读取待删除的行信息输出提示。

以下是经过验证可正常运行的实现代码，针对Authors表做适配：

GO
CREATE OR ALTER TRIGGER Trigger_2 ON Authors
INSTEAD OF DELETE
AS BEGIN
    DECLARE @deleted_id INT;
    DECLARE cursor_deleted CURSOR
    FOR SELECT au_id FROM deleted;

    OPEN cursor_deleted;
    FETCH NEXT FROM cursor_deleted INTO @deleted_id;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT('Attempting to delete author ' + STR(@deleted_id));
        FETCH NEXT FROM cursor_deleted INTO @deleted_id;
    END;

    CLOSE cursor_deleted;
    DEALLOCATE cursor_deleted;
END;

GO
-- 测试删除语句，不会真的删除数据，只会打印待删除的ID
DELETE FROM Authors WHERE au_id BETWEEN 1 AND 10;

逻辑说明：

• 触发器触发后会读取系统自动生成的deleted临时表，其中存储了本次DELETE语句命中的所有待删除行
• 示例中通过游标遍历所有待删除行的au_id字段，逐行打印删除提示
• 全程没有执行实际的删除操作，原表数据不会发生变化

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