如何在表上设置自动提交-SQL Server

Question

我有一个存储过程，如果出现任何错误或在任何进程之间终止过程，我只需要提交ERROR_LOG表。在这种情况下，除了ERROR_LOG表插入之外，所有其他更改都必须回滚。

如何实施？

Answer 1

由于表变量不参与事务，因此可以使用这个事实来实现您的目标：

DECLARE @ErrorLog TABLE ( ID INT )
BEGIN TRANSACTION
INSERT  INTO @ErrorLog
        ( ID )
VALUES  ( 1 ),
        ( 2 )
ROLLBACK TRANSACTION
SELECT  *
FROM    @ErrorLog

输出：

ID
1
2

所以，在您的存储过程中，不是在实际的表中插入表变量，而是在错误时插入实际的表：

CREATE PROCEDURE spTest
AS
BEGIN TRY
   DECLARE @ErrorLog TABLE(...)
   BEGIN TRANSACTION
   INSERT INTO @ErrorLog VALUES(...)
   COMMIT TRANSACTION      
END TRY
BEGIN CATCH
   ROLLBACK TRANSACTION
   INSERT INTO ErrorLog SELECT * FROM @ErrorLog
END CATCH