CREATE TRIGGER [更新分仓库存表] ON dbo.事务明细
FOR INSERT, UPDATE, DELETE
AS
/* 如果是未生效的，不做处理即可.
* 如果是已生效的，作为供应者的，Inserted 要减库存，Deleted 要加库存．
* 如果是已生效的，作为接受者的，Inserted 要加库存，Deleted 要减库存．
增加了对虚实的判断，只有 虚实 = 1，即执行单才能改变库存。这样就不需要判断单据属性了。
增加了对 已接收，已发出 字段的判断。并做了测试。
*/
/* 其它出入仓单，可能造成客户或者供应商有货物库存。
*/
SELECT 仓库Id_, 货物Id_, SUM(数量) AS 变动数量 INTO #库存变动
FROM
(
SELECT 物id_ AS 货物Id_, 接收者Id_ AS 仓库Id_, 数量, 虚实
FROM Inserted INNER JOIN 实体 ON Inserted.接收者Id_ = 实体.Id_
WHERE Inserted.生效=1 And Inserted.已接收 = 1
UNION ALL
SELECT 物id_ AS 货物Id_, 接收者Id_ AS 仓库Id_, - 数量, 虚实
FROM Deleted INNER JOIN 实体 ON Deleted.接收者Id_ = 实体.Id_
WHERE Deleted.生效=1 And Deleted.已接收 = 1
UNION ALL
SELECT 物id_ AS 货物Id_, 供应者Id_ AS 仓库Id_, - 数量, 虚实
FROM Inserted INNER JOIN 实体 ON Inserted.供应者Id_ = 实体.Id_
WHERE Inserted.生效=1 And Inserted.已发出 = 1
UNION ALL
SELECT 物id_ AS 货物Id_, 供应者Id_ AS 仓库Id_, 数量, 虚实
FROM Deleted INNER JOIN 实体 ON Deleted.供应者Id_ = 实体.Id_
WHERE Deleted.生效=1 And Deleted.已发出 = 1
) AS curTemp
WHERE (虚实 = 1)
GROUP BY 货物Id_, 仓库Id_
----------------------------------------
IF @@ROWCOUNT = 0
RETURN
/* 已有库存数据, 则更新 */
UPDATE 实体_分仓库存表
SET 库存数量 = 库存数量 + #库存变动.变动数量
FROM 实体_分仓库存表 INNER JOIN #库存变动
ON 实体_分仓库存表.货物Id_ = #库存变动.货物Id_ AND 实体_分仓库存表.仓库Id_ = #库存变动.仓库Id_
--------------------------------------------
/* 没有库存数据的, 则 Insert 一条记录 */
INSERT INTO 实体_分仓库存表 (货物Id_, 仓库Id_, 库存数量)
SELECT #库存变动.货物Id_, #库存变动.仓库Id_, #库存变动.变动数量
FROM #库存变动
WHERE NOT EXISTS (SELECT Id_ FROM 实体_分仓库存表
WHERE 实体_分仓库存表.货物Id_ = #库存变动.货物Id_
AND 实体_分仓库存表.仓库Id_ = #库存变动.仓库Id_)
