使用TRIGGER處理跨資料表的交易

jeffluo

SQL Server TSQL III – TRIGGER WITH CURSOR AND TRANSACTION  

SQL Server T-SQL技巧主題使用TRIGGER處理跨資料表的交易，使用觸發程序與CURSOR

Version: SQL Server

Level: 100

By Lewis Yang (MVP of SQL Server, MCT, OCM) @ TW, lewisdba@gmail.com

Keyword: TRIGGER / CURSOR / BEGIN TRANSACTION

前些日子幫忙解決交易問題，該問題情境需要使用TRIGGER處理跨資料表的交易，特別分享觸發程式處理與CURSOR使用技巧。

use [AdventureWorks2016] go /* drop table subscribe drop table product */ --產品資料表 create table product (product_id int primary key , product_name varchar(100), stock int ) go --訂單資料表 create table subscribe (sub_id int primary key, product_id int constraint fk_product_id foreign key references product(product_id) , amount int) go ---------------------------------------------------------------- --新增instead of 觸發程序來判斷是否資料異動時候，是否庫存量足夠 ---------------------------------------------------------------- create trigger tri_insert on subscribe instead of insert as begin   set nocount on     declare @sub_id int, @product_id int, @amount int   declare icur cursor for select sub_id,product_id,amount from inserted     open icur   fetch next from icur into @sub_id, @product_id,@amount   while(@@FETCH_STATUS=0)   begin           --判斷剩下庫存是否足夠可以扣除，因為在TRIGGER所以不用多餘啟動BEGIN TRAN，因為本身就是implicit transaction        if exists(select * from product where product_id =@product_id and (stock-@amount) >=0)        begin           insert into subscribe(sub_id,product_id,amount)  values(@sub_id,@product_id,@amount)           update product set stock=stock-@amount where product_id=@product_id        end              fetch next from icur into @sub_id, @product_id,@amount   end   close icur   deallocate icur end go ---------------------------------------------------------------- --變更使用AFTER 觸發程序來判斷是否資料異動時候，是否庫存量足夠 ---------------------------------------------------------------- create trigger ti_update on subscribe instead of update as begin   set nocount on     declare @sub_id int, @product_id int, @amount int   declare icur cursor for select sub_id,product_id,amount from inserted     open icur   fetch next from icur into @sub_id, @product_id,@amount   while(@@FETCH_STATUS=0)   begin             --判斷剩下庫存是否足夠可以扣除，因為在TRIGGER所以不用多餘啟動BEGIN TRAN，因為本身就是implicit transaction                declare @old_amount int=(select amount from deleted where sub_id =@sub_id)        if exists(select * from product where product_id =@product_id and (stock-@amount+@old_amount) >=0)        begin           update subscribe set  amount=@amount  where  sub_id =@sub_id           update product set stock=stock-@amount+@old_amount where product_id=@product_id        end              fetch next from icur into @sub_id, @product_id,@amount   end   close icur   deallocate icur end go --使用觸發程序退掉所有的delete動作 create trigger tri_delete on subscribe after delete as begin   set nocount on     rollback end go --驗證 insert into product(product_id,product_name,stock) values(100,'iPHONE6',100) insert into product(product_id,product_name,stock) values(200,'iPHONE7',100) GO --新增訂單，自動修改庫存 insert into subscribe(sub_id,product_id,amount) values(1,100,40) insert into subscribe(sub_id,product_id,amount) values(2,100,10) select * from product select * from subscribe GO update subscribe set amount=25 where sub_id=1 GO

結論

基本上使用觸發程序可以省下前端程式處理很多複雜的資料庫邏輯。但是使用過多的TRIGGER會導致交易範圍延長，鎖定過長，進而影響資料庫效能。

Lewis Yang @ TW 2016/07/08