W
wellknow
Unregistered / Unconfirmed
GUEST, unregistred user!
--下面是我的一个更新总库存数量的触发器
--在期初库存明细 StoresBalD 商品库存数量 Qty 改变时改变库存表 Onhand
CREATE TRIGGER [StoresBalD2Onhand] ON [StoresBalD]
FOR INSERT, UPDATE, Delete
AS
SET NOCOUNT ON
/*
***********************Onhand表结构***********************
StoresID:Integer
GoodsID:Varchar
Onhand:Float
*/
select Id, ItemNo, GoodsID, fldUpdateQty=sum(Quantity)
into #tmpUpdate__
from
(select Id, ItemNo, GoodsID, Quantity from Inserted
Union all
select Id, ItemNo, GoodsID, -Quantity from Deleted
)as tblUpdate__
Group by
Id, ItemNo, GoodsID
UPDATE Onhand
SET Onhand = oh. Onhand + tm.fldUpdateQty
from #tmpUpdate__ tm right Join Onhand oh
on oh. StoresID = -tm. id
INSERT INTO Onhand
select -Id, GoodsID, fldUpdateQty
from #tmpUpdate__
where -id not in
(select storesID from Onhand )
--drop table #tmpUpdate__
难道在SQL Server 2000中不能用Select ... INTO ... 吗?
--在期初库存明细 StoresBalD 商品库存数量 Qty 改变时改变库存表 Onhand
CREATE TRIGGER [StoresBalD2Onhand] ON [StoresBalD]
FOR INSERT, UPDATE, Delete
AS
SET NOCOUNT ON
/*
***********************Onhand表结构***********************
StoresID:Integer
GoodsID:Varchar
Onhand:Float
*/
select Id, ItemNo, GoodsID, fldUpdateQty=sum(Quantity)
into #tmpUpdate__
from
(select Id, ItemNo, GoodsID, Quantity from Inserted
Union all
select Id, ItemNo, GoodsID, -Quantity from Deleted
)as tblUpdate__
Group by
Id, ItemNo, GoodsID
UPDATE Onhand
SET Onhand = oh. Onhand + tm.fldUpdateQty
from #tmpUpdate__ tm right Join Onhand oh
on oh. StoresID = -tm. id
INSERT INTO Onhand
select -Id, GoodsID, fldUpdateQty
from #tmpUpdate__
where -id not in
(select storesID from Onhand )
--drop table #tmpUpdate__
难道在SQL Server 2000中不能用Select ... INTO ... 吗?