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

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

 if exists(select 1 from inserted)
  BEGIN
  select @SaleShop=SaleShop from inserted
  select @minDfJjSale=min(DfJjSale),@maxDfJjSale=max(DfJjSale) from Tab_SaleAndCarStyle where Saleshop=@Saleshop and state>0 and price_ver=1 group by SaleShop
      if(@minDfJjSale=0 and @maxDfJjSale=0)
   set @isDfjj='否'
   else
   set @isDfjj='是'
   update dbo.TAB_PARTNER  
   set PriceRange=ltrim(@minDfJjSale)+'~'+ltrim(@maxDfJjSale),ispartneraddprice=@isDfjj
   where ID= @saleshop
  END

  else
 if exists (select 1 from deleted)
  BEGIN
  select @SaleShop=SaleShop from deleted
  select @minDfJjSale=min(DfJjSale),@maxDfJjSale=max(DfJjSale) from Tab_SaleAndCarStyle where Saleshop=@Saleshop and state>0 and price_ver=1 group by SaleShop
   if(@minDfJjSale=0 and @maxDfJjSale=0)
   set @isDfjj='否'
   else
   set @isDfjj='是'
   update dbo.TAB_PARTNER  
   set PriceRange=ltrim(@minDfJjSale)+'~'+ltrim(@maxDfJjSale),ispartneraddprice=@isDfjj
   where ID= @saleshop
  END

转载于:https://www.cnblogs.com/yinsu12311/p/4385507.html