有时候为了数据完整我们会启用到事务。正常的时候一帆风顺，如果rollback 呢？
最简单的一个回滚
IF OBJECT_ID('PROC1') IS NOT NULL
DROP PROCEDURE dbo.PROC1;
GO
CREATE PROCEDURE PROC1
AS
BEGIN
BEGIN TRY
BEGIN TRAN;
SELECT 'Tran1' ,1;
RAISERROR(16,1,1);
SELECT 'Tran1' ,2;
COMMIT TRAN;
END TRY
BEGIN CATCH
SELECT 'Tran1' ,3;
ROLLBACK TRAN;
SELECT 'Tran1' ,4;
RETURN -1;
END CATCH;
END;
GO
执行一下，抛出错误后继续向下执行。
当事务有嵌套的时候。在抛出错误的事务里面，会继续执行批处理剩下的语句，最后才回滚上去并往上层抛错误。非抛出层和最外层事务，语句运行至rollback就往上层抛。
如下
IF OBJECT_ID('PROC2') IS NOT NULL
DROP PROCEDURE dbo.PROC2
go
CREATE PROCEDURE PROC2
AS
BEGIN
BEGIN TRY
BEGIN TRAN
SELECT 'Tran2',1
EXEC dbo.PROC1
SELECT 'Tran2',2
COMMIT TRAN
END TRY
BEGIN CATCH
SELECT 'Tran2',3
ROLLBACK TRAN
SELECT 'Tran2',4
RETURN -2
END CATCH
END
GO
IF OBJECT_ID('PROC3') IS NOT NULL
DROP PROCEDURE dbo.PROC3
go
CREATE PROCEDURE PROC3
AS
BEGIN
BEGIN TRY
BEGIN TRAN
SELECT 'PROC3',1
EXEC dbo.PROC2
SELECT 'PROC3',2
COMMIT TRAN
END TRY
BEGIN CATCH
SELECT 'PROC3',3
ROLLBACK TRAN
SELECT 'PROC3',4
RETURN -3
END CATCH
END
GO
执行存储过程
SET NOCOUNT ON;
DECLARE @INT INT
EXEC @INT=dbo.PROC3
SELECT @INT
rollback在嵌套触发器中，如果在触发器的事务里面抛出错误，回滚之前触发器的语句修改，继续执行rollback之后的语句。但并不会激活之后的触发器
CREATE TABLE TestTR
(
ID INT PRIMARY KEY,
Name NVARCHAR(50)
)
CREATE TABLE TRTB1
(
ID INT PRIMARY KEY,
Col1 INT
)
CREATE TRIGGER TR_TESTTR1 ON dbo.TestTR AFTER
INSERT AS
BEGIN TRY
BEGIN TRAN
INSERT INTO dbo.TRTB1
( ID, Col1 )
VALUES ( 1,
CONVERT(INT,'a') )
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
INSERT INTO dbo.TestTR
( ID, Name )
VALUES ( 1,
CONVERT(INT,'2') )
END CATCH
GO
INSERT INTO TestTR ( ID, Name )
VALUES ( 4,
CONVERT(INT,'3') )
- 1楼htmlhtml
- mark~~~~~，，，可以一起交流吗，，技术群：452982916