[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 |
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 |
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 |
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 |
所以這裡想驗證的是
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了