正确使用 SQL Server 中的事务
我有 2 个命令，需要它们都正确执行或都不执行.所以我觉得我需要一个事务，但我不知道如何正确使用它.
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.
下面的脚本有什么问题?
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
INSERT命令执行了，但是UPDATE命令有问题.
The INSERT command is executed, but the UPDATE command has a problem.
如果其中任何一个命令在执行时出错，我如何实现它以回滚这两个命令?
How can I implement this to rollback both commands if any of them have an error in execution?
推荐答案
添加一个 try/catch 块，如果事务成功则提交更改，如果事务失败则回滚事务:
Add a try/catch block, if the transaction succeeds it will commit the changes, if the transaction fails the transaction is rolled back:
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
相关文章