触发器的应用

--当在“出货表_明细”增、删、改时，相应地在“订单表_明细”对已交货数量和未交货数量作出修改。
Alter trigger tr_出货表_明细
on 出货表_明细
For DELETE,INSERT,UPDATE
as
Declare @JiaoHuo varchar(20)
 Begin
 Set @JiaoHuo = (Select Sum(交货数量) from 出货表_明细 Group by 序号 Having 序号 = (Select 序号 From Deleted))
 Set @JiaoHuo = ISNULL(@JiaoHuo,(Select Sum(交货数量) from 出货表_明细 Group by 序号 Having 序号 = (Select 序号 From Inserted)))
        Set @JiaoHuo = ISNULL(@JiaoHuo,0)
 UPDATE N SET 已交货数量 = @JiaoHuo, 未交货数量 = N.订购数量 - @JiaoHuo
                From 订单表_明细 AS N INNER Join Deleted AS D
      ON N.序号 = D.序号
        End

 

--当“订单表_明细”中增、删、改时、判断该笔订单的未交货数量合计是否为0，修改“订单表”的订单状态为“已完成”，否则为“未完成”
Create trigger tr_Status
ON 订单表_明细
FOR INSERT,UPDATE,DELETE
AS
Declare @Order varchar(20)
 Begin
        Set @Order = (Select 订单号 from Deleted)
 Set @Order = ISNULL(@Order,(Select 订单号 from Inserted))
 If(Select Sum(未交货数量) From 订单表_明细 Group by 订单号 Having 订单号 = @Order)= 0
  Begin 
   UPDATE 订单表 Set 订单状态 = '已完成',完成日期 = GetDate() Where 订单号 = @Order
  End
 Else
  Begin  
   UPDATE 订单表 Set 订单状态 = '未完成',完成日期 = NUll Where 订单号 = @Order
  End
 End