to rea1gz(冒牌realgz V0.3) :1,我尝试过在InventoryMaster加Update,Delete触发器,Update还好办,但Delete就不行了!因为前台UI更新数据时如果有删除的数据,会先删除明细表数据.这样就更新不了!2,没错,出入库是要更新库存.我的Material 表有两个库存字段"帐面库存量","实际库存量",盘点单更新的是"实际库存量",明细里有个选项"更新账面库存",可选更新"帐面库存".3,我的表没有"审核日期"字段,我想审核时间没关系!目前想实现的就是您所说的"没审核/反审核就不能更新库存".盘点单只对仓库的实际库存量做更新,跟出入库没关系.
大概写一下:create trigger tr_InventoryMaster_Update on InventoryMaster for update asif not update(AuditStatus) returnupdate a set StockNumber=a.StockNumber+t.Number --这里还是不很明白你需要的算法 from Material a,( select MaterialID,sum(case when i.AuditStatus is not null and d.AuditStatus is null then Number when i.AuditStatus is null and d.AuditStatus is not null then -Number else 0 end) as Number from insertd i,deleted d,InventoryDetail b where i.id=d.id and i.id=b.InventoryID group by MaterialID ) as t where a.id=t.MaterialID/* 可能你需要以下语句 update a set StockNumber=t.Number from Material a,( select MaterialID,sum(case when i.AuditStatus is not null and d.AuditStatus is null then Number else 0 end) as Number from insertd i,deleted d,InventoryDetail b where i.id=d.id and i.id=b.InventoryID group by MaterialID ) as t where a.id=t.MaterialID */go
3
问题不管了对于1的问题
你可以在InventoryDetail加insert、update、delete触发器,而在InventoryMaster表只加update触发器,而且只有修改AuditStatus字段才触发
on InventoryMaster
for update
asif not update(AuditStatus)
returnupdate a
set StockNumber=a.StockNumber+t.Number --这里还是不很明白你需要的算法
from Material a,(
select MaterialID,sum(case when i.AuditStatus is not null and d.AuditStatus is null then Number when i.AuditStatus is null and d.AuditStatus is not null then -Number else 0 end) as Number
from insertd i,deleted d,InventoryDetail b
where i.id=d.id
and i.id=b.InventoryID
group by MaterialID
) as t
where a.id=t.MaterialID/* 可能你需要以下语句
update a
set StockNumber=t.Number
from Material a,(
select MaterialID,sum(case when i.AuditStatus is not null and d.AuditStatus is null then Number else 0 end) as Number
from insertd i,deleted d,InventoryDetail b
where i.id=d.id
and i.id=b.InventoryID
group by MaterialID
) as t
where a.id=t.MaterialID
*/go