[SQL Server]Stored Procedure & Trigger RAISERROR小試驗

最近遇到了一個stored procedure結合trigger的case有點小複雜，因為不是很肯定確切的執行流程跟結果，所以就去掉商業邏輯做個簡單的sample來模擬進行測試，情境是這樣的:

首先去執行一個stored procedure(sp_Test1)，而sp_Test1有個transaction 裡面要執行另外兩個stored procedure(sp_Test2、sp_Test3)，然後如果進到catch則rollback transaction，另外還有個output parameter用來傳回sp_Test2新增的Id值，SQL語法如下

	CREATE PROCEDURE [dbo].[sp_Test1]
	@table1_Id INT OUTPUT
	AS
	begin try
	begin transaction
	print 'sp_Test1:executing sp_Test2'
	exec sp_Test2 @table1_Id output
	print 'sp_Test1:executing sp_Test3'
	exec sp_Test3
	print 'commit tran'
	commit transaction
	end try
	begin catch
	rollback transaction
	print 'sp_Test1:in catch'
	RAISERROR('raiseerror:sp_Test1', 16, 1)
	end catch

view raw sp_Test1.sql hosted with ❤ by GitHub

sp_Test2: insert資料到testTable1並傳回IDENTITY值，傳回的值在sp_Test1有@table1_Id參數會output回去

	CREATE PROCEDURE [dbo].[sp_Test2]
	@table1_Id INT OUTPUT
	AS
	print 'sp_Test2:insert'
	insert into testTable1 values('col1')
	select @table1_Id = @@IDENTITY

view raw sp_Test2.sql hosted with ❤ by GitHub

sp_Test3: update testTable2的Num欄位值為6，但testTable2有個trigger (NumTrigger)，當update時會去判斷如果新的Num>5就RAISERROR並rollback

	CREATE PROCEDURE [dbo].[sp_Test3]
	AS
	begin try
	print 'sp_Test3:updating'
	update testTable2 set Num=6 where Id=2
	print 'sp_Test3:updated'
	end try
	begin catch
	print 'sp_Test3:in catch'
	RAISERROR('raiseerror:sp_Test3', 16, 1)
	end catch

view raw sp_Test3.sql hosted with ❤ by GitHub

testTable1的schema


testTable2的schema


新增一筆資料讓testTable有資料可以做update


NumTrigger主要判斷如果新增的num>5就RAISERROR

	CREATE TRIGGER [dbo].[NumTrigger]
	ON [dbo].[testTable]
	FOR UPDATE
	AS
	BEGIN
	print 'begin trigger'
	if UPDATE(Num)
	BEGIN
	DECLARE @num int
	select @num=Num from inserted
	print 'inserted num:' + cast(@num as nvarchar)
	if @num>5
	BEGIN
	print 'raiseerror in trigger'
	RAISERROR('Error:Trigger', 16, 2)
	rollback
	End
	End
	END

view raw NumTrigger.sql hosted with ❤ by GitHub

所以這裡想驗證的是
1.如果在trigger中發生error，那sp_Test1應該會進到catch裡去rollback transaction?(就理論上來說應該要會)
2.如果rollback了那output 參數table1_Id回傳的值是?
測試用的資料庫我就直接拿一個之前已經放在Azure上的測試DB用，然後執行如下圖的指令


執行結果如下


從結果可以看出在sp_Test3做update時因為觸發trigger沒過丟回error進到catch，然後catch裡RAISERROR則回到sp_Test1的catch，所以rollback transaction，整個流程跟理論上的認知是一樣沒錯的。
而另外原本先做了insert回傳的table1_Id值(auto increment)則是跟rollback前一樣(範例中上個seed到7)，但去查詢table並沒有資料被加入，也就是說insert的資料被rollback但auto increment的seed不會跟著復原，所以當下次新增一筆資料到table1時，ID值就會為9了