SQLServer 触发器----增删改触发,两张表

9/1/2015来源:SQL技巧人气:1000

SQLServer 触发器----增删改触发,两张表

ALTER TRIGGER [dbo].[PRiceRange]ON[dbo].[Tab_SaleAndCarStyle]for update,insert,deleteASdeclare @Saleshop int,@minDfJjSale decimal(18,3),@maxDfJjSale decimal(18,3),@isDfjj varchar(50)set @isDfjj='否'

if exists(select 1 from inserted)BEGINselect @SaleShop=SaleShop from insertedselect @minDfJjSale=min(DfJjSale),@maxDfJjSale=max(DfJjSale) from Tab_SaleAndCarStyle where Saleshop=@Saleshopand state>0 and price_ver=1 group by SaleShopif(@minDfJjSale=0 and @maxDfJjSale=0)set @isDfjj='否'elseset @isDfjj='是'update dbo.TAB_PARTNERset PriceRange=ltrim(@minDfJjSale)+'~'+ltrim(@maxDfJjSale),ispartneraddprice=@isDfjjwhere ID= @saleshopEND

elseif exists (select 1 from deleted)BEGINselect @SaleShop=SaleShop from deletedselect @minDfJjSale=min(DfJjSale),@maxDfJjSale=max(DfJjSale) from Tab_SaleAndCarStyle whereSaleshop=@Saleshopand state>0 and price_ver=1 group by SaleShopif(@minDfJjSale=0 and @maxDfJjSale=0)set @isDfjj='否'elseset @isDfjj='是'update dbo.TAB_PARTNERset PriceRange=ltrim(@minDfJjSale)+'~'+ltrim(@maxDfJjSale),ispartneraddprice=@isDfjjwhere ID= @saleshopEND