正确使用 SQL Server 中的事务
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10153648/
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
Correct use of transactions in SQL Server
提问by Saeid
I have 2 commands and need both of them executed correctly or none of them executed. So I think I need a transaction, but I don't know how to use it correctly.
我有 2 个命令，需要它们都正确执行或都不执行。所以我想我需要一个事务，但我不知道如何正确使用它。
What's the problem with the following script?
以下脚本有什么问题？
BEGIN TRANSACTION [Tran1]
INSERT INTO [Test].[dbo].[T1]
([Title], [AVG])
VALUES ('Tidd130', 130), ('Tidd230', 230)
UPDATE [Test].[dbo].[T1]
SET [Title] = N'az2' ,[AVG] = 1
WHERE [dbo].[T1].[Title] = N'az'
COMMIT TRANSACTION [Tran1]
GO
The INSERTcommand is executed, but the UPDATEcommand has a problem.
该INSERT指令被执行，但该UPDATE命令有一个问题。
How can I implement this to rollback both commands if any of them have an error in execution?
如果其中任何一个在执行时出错，我该如何实现它来回滚这两个命令？
回答by Darren
Add a try/catch block, if the transaction succeeds it will commit the changes, if the transaction fails the transaction is rolled back:
添加一个 try/catch 块，如果事务成功则提交更改，如果事务失败则回滚事务：
BEGIN TRANSACTION [Tran1]
BEGIN TRY
INSERT INTO [Test].[dbo].[T1] ([Title], [AVG])
VALUES ('Tidd130', 130), ('Tidd230', 230)
UPDATE [Test].[dbo].[T1]
SET [Title] = N'az2' ,[AVG] = 1
WHERE [dbo].[T1].[Title] = N'az'
COMMIT TRANSACTION [Tran1]
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION [Tran1]
END CATCH
回答by Nikola Markovinovi?
At the beginning of stored procedure one should put SET XACT_ABORT ONto instruct Sql Server to automatically rollback transaction in case of error. If ommited or set to OFF one needs to test @@ERRORafter each statement or use TRY ... CATCH rollbackblock.
在存储过程开始时，应该设置SET XACT_ABORT ON，以指示Sql Server 发生错误时自动回滚事务。如果省略或设置为 OFF，则需要在每条语句后测试@@ERROR或使用TRY ... CATCH 回滚块。
回答by Bohdan
Easy approach:
简单的方法：
CREATE TABLE T
(
C [nvarchar](100) NOT NULL UNIQUE,
);
SET XACT_ABORT ON -- Turns on rollback if T-SQL statement raises a run-time error.
SELECT * FROM T; -- Check before.
BEGIN TRAN
INSERT INTO T VALUES ('A');
INSERT INTO T VALUES ('B');
INSERT INTO T VALUES ('B');
INSERT INTO T VALUES ('C');
COMMIT TRAN
SELECT * FROM T; -- Check after.
DELETE T;
