[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語法如下

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

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

testTable1的schema


testTable2的schema


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


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

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