34,593
社区成员
发帖
与我相关
我的任务
分享
set xact_abort on
begin tran
exec p_1
exec p_2
commit tran
--完整測試示例
--1)建立測試表
CREATE TABLE tb1 (id INT,field1 TINYINT)
INSERT tb1
SELECT 1,1
UNION ALL
SELECT 2,2
CREATE TABLE tb2 (id INT,field2 TINYINT)
INSERT tb2
SELECT 1,1
UNION ALL
SELECT 2,2
GO
--2)建立測試存儲過程
CREATE PROC [dbo].[up_test1]
AS
begin
declare @err int
UPDATE tb1 SET field1 = 11
WHERE id = 1
SET @err=@@ERROR
IF @err>0
BEGIN
SELECT @err
RETURN
END
EXEC up_test2
SELECT @@ERROR
end
GO
CREATE PROC [dbo].[up_test2]
AS
begin
UPDATE tb2 SET field2 = 322
WHERE id = 2
end
GO
--3)執行事務
DECLARE @t TABLE (err INT)
BEGIN TRAN
INSERT @t exec up_test1
IF (select err FROM @t)>0
begin
ROLLBACK TRAN
PRINT '失敗'
end
ELSE
begin
COMMIT TRAN
PRINT '成功'
end
CREATE PROC up_test1
AS
UPDATE tb1 SET field1 = 1
WHERE id = 1
EXEC up_test2
GO
CREATE PROC up_test2
AS
UPDATE tb2 SET field2 = 2
WHERE id = 2
GO
--显示声明事务,无论有多少代码,都放在begin tran和commit tran之间就好了.
--切记,不要嵌套事务。如果嵌套事务,异常时执行的第一个ROLLBACK语句会回滚所有的事务
--#1.在最外层用TRY...CATCH...
BEGIN TRY
BEGIN TRAN
EXEC up_test1
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
--#2.用SER XACT_ABORT ON环境选项
SET XACT_ABORT ON
BEGIN TRAN
EXEC up_test1
COMMIT TRAN