Server在触发器内游标中的错误处理

Question

我是SQL Server错误处理的新手，我的英语不太清楚，所以我对任何错误表示歉意。

问题是:我将多条记录插入到一个表中。该表有一个AFTER INSERT触发器，它使用游标逐个处理FETCH循环中的记录。如果有什么错误发生，一切都会倒退。因此，如果插入的记录中只有一个错误的字段，我就会丢失所有的记录。插入也会回滚，所以我找不到错误的记录。因此，我需要处理光标内的错误，只回滚错误的记录。

我用3个表创建了一个测试数据库：

tA

VarSmallint smallint
VarTinyint tinyint
String varchar(20)

tB

ID int (PK, identity)
Timestamp datetime (default: getdate())
VarSmallint smallint
VarTinyint tinyint
String varchar(20)

tC

ID int PK
Timestamp datetime
VarTinyint1 tinyint
VarTinyint2 tinyint
String varchar(10)

tA包含3条记录和1条错误记录。我将此内容插入到tB中。tB拥有触发器，并将记录插入到tC ony中。

tC只有微小的变量，所以插入大于255的值可能会出现问题。这是为测试而发生错误的地方！

我的扳机是：

ALTER TRIGGER [dbo].[trg_tB] 
ON  [dbo].[tB] 
AFTER INSERT
AS 
BEGIN
    IF @@rowcount = 0
        RETURN;

    SET NOCOUNT ON;

    DECLARE
         @ID                AS int,
         @Timestamp     AS datetime,
         @VarSmallint   AS smallint,
         @VarTinyint        AS tinyint,
         @String            AS varchar(20),

    DECLARE curNyers CURSOR DYNAMIC
    FOR
        SELECT
            [ID], [Timestamp], [VarSmallint], [VarTinyint], [String]
        FROM INSERTED
        ORDER BY [ID]

    OPEN curNyers

    FETCH NEXT FROM curNyers INTO @ID, @Timestamp, @VarSmallint, @VarTinyint, @String

    WHILE @@FETCH_STATUS = 0
    BEGIN
    BEGIN TRY
        BEGIN TRAN

        INSERT INTO [dbo].[tC]([ID], [Timestamp], [VarTinyint1], [VarTinyint2], [String])
        VALUES (@ID, @Timestamp, @VarSmallint, @VarTinyint, @String)

        COMMIT TRAN
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN

        INSERT INTO [dbo].[tErrorLog]([ErrorTime], [UserName], [ErrorNumber],
                                      [ErrorSeverity], [ErrorState],
                                      [ErrorProcedure], [ErrorLine],
                                      [ErrorMessage], [RecordID])
        VALUES (SYSDATETIME(), SUSER_NAME(), ERROR_NUMBER(),
                ERROR_SEVERITY(), ERROR_STATE(),
                ERROR_PROCEDURE(), ERROR_LINE(),
                ERROR_MESSAGE(), @ID)

        END CATCH

        FETCH NEXT FROM curNyers INTO @ID, @Timestamp, @VarSmallint, @VarTinyint, @String
    END

    CLOSE curNyers
    DEALLOCATE curNyers
END

如果我插入了两个好的记录，其中有一个错误，那么一切都在倒退，我得到了一个错误：

Msg 3609，16级，状态1，第1行 事务在触发器中结束。批处理已被中止。

请帮帮我!如何修改此触发器以使其工作正常？

如果我插入了错误的记录，我需要：

1. tB中插入的所有记录
2. tC中所有的好记录
3. 在tErrorLog中登录错误

谢谢!

Answer 1

我删除了TRIGGER，并将代码复制到STORED PROCEDURE中。

然后，我将行Status添加到tB中，并将默认值设置为0。

1是“记录处理正常”，2是“记录处理错误”。

我用WHERE Status = 0填充光标。

在TRY部分中，我将状态更新为1，在CATCH节中，将状态更新为2。

我没有作业，所以我使用带有SQLCMD命令的批处理文件从Windows调度程序运行SP。

现在的处理工作很好，而且它第一次工作得很好。谢谢你的帮助！

Answer 2

您的触发器中有和两大灾难：

1. 不要在触发器内使用游标--这太可怕了！每当给定的操作发生时，触发器就会触发--你几乎控制不了它们何时发射和发射多少次。因此，为了不对系统性能造成太大影响，触发器应该是非常小、快速、灵活的--做而不是在触发器中做任何繁重的操作和广泛的处理。游标是，但是是灵活和快速的--它是一个资源占优势、处理器占上风、内存泄漏的怪物--尽可能避免使用，而且最明确的是在触发器内！(而且你不需要它们，不管怎么说，99%的病例) 您可以将整个逻辑重写为以下一条简单、快速、基于集合的语句： 在插入后更改dbo.tB上的触发器dbo.tB作为开始插入dbo。tC选择ID、时间戳、VarSmallint、VarTinyint、字符串从插入的结束。
2. 永远不要在触发器内调用COMMIT TRAN。触发器在导致其触发的语句的上下文和事务中执行--如果一切正常，就让触发器完成，然后事务就会被提交。如果需要中止，请调用ROLLBACK。但是从来没有在触发器中间调用COMMIT TRAN。只是不要..。