SQL 触发错误：当前事务无法提交，不支持写入日志文件的操作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15980318/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Trigger Error: The current transaction cannot be committed and cannot support operations that write to the log file
提问by Denis
So I am getting the following error message from SQL Server when sp_SomeProc tries to execute an invalid sql statement. I get the error:
因此，当 sp_SomeProc 尝试执行无效的 sql 语句时，我从 SQL Server 收到以下错误消息。我收到错误：
The current transaction cannot be committed and cannot support operations that write to the log file.
Any ideas on what I am doing wrong? (this is just a sample that I created to mimic the problem so please no "why are you doing this?", "this has security implications", etc..)
关于我做错了什么的任何想法？（这只是我为模拟问题而创建的示例，所以请不要“你为什么这样做？”、“这有安全隐患”等。）
So my table looks like:
所以我的表看起来像：
CREATE TABLE tSOMETABLE
(
RecID INT NOT NULL IDENTITY(1,1)
Val VARCHAR(20),
CONSTRAINT [PK_tSOMETABLE] PRIMARY KEY CLUSTERED
(
RecID ASC
)
)
So in my trigger I have:
所以在我的触发器中，我有：
CREATE TRIGGER [dbo].[TR_tSOMETABLE_INSERT]
ON [dbo].[tSOMETABLE]
FOR INSERT
AS
SET NOCOUNT ON
BEGIN
BEGIN
SELECT * INTO #temp FROM INSERTED
WHILE EXISTS (SELECT 1 FROM #temp)
BEGIN
DECLARE @RecID INT
SELECT @RecID = RecID
FROM #temp t
EXEC dbo.sp_SomeProc @EventType = 'ON INSERT', @RecID = @RecID
DELETE #temp WHERE @RecID = RecID
END
END
END
Now the code of sp_SomeProc looks like:
现在 sp_SomeProc 的代码如下所示：
CREATE PROC sp_SomeProc
(
@EventType VARCHAR(50),
@RecID INT,
@Debug BIT = 0
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @ProcTable TABLE
(
RecID INT NOT NULL IDENTITY(1,1),
Cmd VARCHAR(MAX)
)
INSERT INTO @ProcTable(Cmd)
SELECT 'EXEC sp_who'
UNION
SELECT 'EXEC sp_SomeStoredProcThatDoesntExist'
DECLARE @RecID INT
SELECT @RecID = MIN(RecID) FROM @ProcTable
WHILE @RecID IS NOT NULL
BEGIN
DECLARE @sql VARCHAR(MAX)
SELECT @sql = cmd FROM @ProcTable WHERE RecID = @RecID
IF @Debug = 1
PRINT @sql
ELSE
BEGIN
BEGIN TRY
EXEC(@sql)
END TRY
BEGIN CATCH
DECLARE @Msg VARCHAR(MAX), @ErrorNumber INT, @ErrorSeverity INT, @ErrorState int, @ErrorProcedure nvarchar(256), @ErrorLine int, @ErrorMessage nvarchar(MAX)
SELECT @Msg = 'Failed While Executing: ' + @sql
SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorProcedure = ERROR_PROCEDURE(), @ErrorLine = ERROR_LINE(), @ErrorMessage = ERROR_MESSAGE()
-- DO SOME MORE STUFF HERE AND THEN ...
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH
END
SELECT @RecID = MIN(RecID) FROM @ProcTable WHERE RecID > @RecID
END
END
So to test I try:
所以为了测试我尝试：
INSERT INTO tSOMETABLE(Val)
SELECT 'Hello'
回答by StrayCatDBA
This error occurs when you use a try/catch block inside of a transaction. Let's consider a trivial example:
当您在事务中使用 try/catch 块时会发生此错误。让我们考虑一个简单的例子：
SET XACT_ABORT ON
IF object_id('tempdb..#t') IS NOT NULL
DROP TABLE #t
CREATE TABLE #t (i INT NOT NULL PRIMARY KEY)
BEGIN TRAN
INSERT INTO #t (i) VALUES (1)
INSERT INTO #t (i) VALUES (2)
INSERT INTO #t (i) VALUES (3)
INSERT INTO #t (i) VALUES (1) -- dup key error, XACT_ABORT kills the batch
INSERT INTO #t (i) VALUES (4)
COMMIT TRAN
SELECT * FROM #t
When the fourth insert causes an error, the batch is terminated and the transaction rolls back. No surprises so far.
当第四次插入导致错误时，批处理终止并且事务回滚。到目前为止没有任何惊喜。
Now let's attempt to handle that error with a TRY/CATCH block:
现在让我们尝试使用 TRY/CATCH 块处理该错误：
SET XACT_ABORT ON
IF object_id('tempdb..#t') IS NOT NULL
DROP TABLE #t
CREATE TABLE #t (i INT NOT NULL PRIMARY KEY)
BEGIN TRAN
INSERT INTO #t (i) VALUES (1)
INSERT INTO #t (i) VALUES (2)
BEGIN TRY
INSERT INTO #t (i) VALUES (3)
INSERT INTO #t (i) VALUES (1) -- dup key error
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()
END CATCH
INSERT INTO #t (i) VALUES (4)
/* Error the Current Transaction cannot be committed and
cannot support operations that write to the log file. Roll back the transaction. */
COMMIT TRAN
SELECT * FROM #t
We caught the duplicate key error, but otherwise, we're not better off. Our batch still gets terminated, and our transaction still gets rolled back. The reason is actually very simple:
我们发现了重复键错误，但除此之外，我们的情况也不好。我们的批处理仍然被终止，我们的事务仍然被回滚。原因其实很简单：
TRY/CATCH blocks don't affect transactions.
TRY/CATCH 块不影响交易。
Due to having XACT_ABORT ON, the moment the duplicate key error occurs, the transaction is doomed. It's done for. It's been fatally wounded. It's been shot through the heart...and the error's to blame. TRY/CATCH gives SQL Server...a bad name. (sorry, couldn't resist)
由于 XACT_ABORT ON，一旦出现重复键错误，事务就注定失败。它已经完成了。它受了致命伤。它被击穿了心脏……而错误是罪魁祸首。TRY/CATCH 给 SQL Server 带来了一个糟糕的名字。（对不起，忍不住）
In other words, it will NEVERcommit and will ALWAYSbe rolled back. All a TRY/CATCH block can do is break the fall of the corpse. We can use the XACT_STATE()function to see if our transaction is committable. If it is not, the only option is to roll back the transaction.
换句话说，它永远不会提交并且总是会被回滚。TRY/CATCH 块所能做的就是阻止尸体的坠落。我们可以使用XACT_STATE()函数来查看我们的事务是否可提交。如果不是，唯一的选择是回滚事务。
SET XACT_ABORT ON -- Try with it OFF as well.
IF object_id('tempdb..#t') IS NOT NULL
DROP TABLE #t
CREATE TABLE #t (i INT NOT NULL PRIMARY KEY)
BEGIN TRAN
INSERT INTO #t (i) VALUES (1)
INSERT INTO #t (i) VALUES (2)
SAVE TRANSACTION Save1
BEGIN TRY
INSERT INTO #t (i) VALUES (3)
INSERT INTO #t (i) VALUES (1) -- dup key error
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()
IF XACT_STATE() = -1 -- Transaction is doomed, Rollback everything.
ROLLBACK TRAN
IF XACT_STATE() = 1 --Transaction is commitable, we can rollback to a save point
ROLLBACK TRAN Save1
END CATCH
INSERT INTO #t (i) VALUES (4)
IF @@TRANCOUNT > 0
COMMIT TRAN
SELECT * FROM #t
Triggers always execute within the context of a transaction, so if you can avoid using TRY/CATCH inside them, things are much simpler.
触发器总是在事务的上下文中执行，因此如果您可以避免在其中使用 TRY/CATCH，事情就会简单得多。
For a solution to your problem, a CLR Stored Proc could connect back to SQL Server in a separate connection to execute the dynamic SQL. You gain the ability to execute the code in a new transaction and the error handling logic is both easy to write and easy to understand in C#.
为了解决您的问题，CLR 存储过程可以在单独的连接中连接回 SQL Server 以执行动态 SQL。您可以获得在新事务中执行代码的能力，并且错误处理逻辑在 C# 中既易于编写又易于理解。
