SQL Server 两个触发器例子，大家看看怎么样？
订单出货时要用到触发器，所以这两天现学现写了两个触发器，测试通过，但总觉得代码不太优美，应该可以写得更好，看看大家有什么看法或意见：
--当在“出货表_明细”增、删、改时，相应地在“订单表_明细”对已交货数量和未交货数量作出修改。
Create 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
======================================================================
--出货表_明细
CREATE TABLE [出货表_明细] (
[序号] [int] NULL ,
[订单号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[出货单号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[料号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[品名_1] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[品名_2] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[批号] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[单位] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[规格型号] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[交货数量] [int] NULL ,
[交货情况] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[客户要求交货期] [datetime] NULL ,
[预交日期] [datetime] NULL ,
[交货日期] [datetime] NULL ,
[sysRowInfo] [text] COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_出货表_明细] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_出货表_出货表_明细] FOREIGN KEY
(
[出货单号]
) REFERENCES [出货表] (
[出货单号]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
--订单表
CREATE TABLE [订单表] (
[订单号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[客户编号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[客户名称] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[下单日期] [datetime] NULL ,
[签收日期] [datetime] NULL ,
[接单业务员] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[客户要求完成日期] [datetime] NULL ,
[完成日期] [datetime] NULL ,
[订单状态] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[总金额] [money] NULL ,
[出货人] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[收货地址] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[收货人] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[备注] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[sysRowInfo] [text] COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_订单表] PRIMARY KEY CLUSTERED
(
[订单号]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
--订单表_明细
CREATE TABLE [订单表_明细] (
[序号] [int] IDENTITY (1, 1) NOT NULL ,
[订单号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[料号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[品名_1] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[品名_2] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[批号] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[单位] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[规格型号] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[订购数量] [int] NULL ,
[已交货数量] [int] NULL ,
[未交货数量] [int] NULL ,
[单价] [money] NULL ,
[合计金额] [money] NULL ,
[客户要求交货期] [datetime] NULL ,
[预交日期] [datetime] NULL ,
[sysRowInfo] [text] COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_订单表_明细] PRIMARY KEY CLUSTERED
(
[序号]
) ON [PRIMARY] ,
CONSTRAINT [FK_订单表_订单表_明细] FOREIGN KEY
(
[订单号]
) REFERENCES [订单表] (
[订单号]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
