＋设为首页 ＋添加到收藏夹
二个表
表1
materiel:主要字段如下
[idno] [char] PRIMARY KEY
[idname] [char]
[unit] [char]
[staut] [char
[kind] [char]
[firstqty][int]
[safeqty] [int]
[quantity][int]
[allotqty][int]
表二
poorders:主要字段如下
[orderno] [char]
[ordeitem][char]
[sort] [tinyint]
[quantity][int]
.....
PRIMARY KEY (orderno,ordeitem)
因为如果表二中的orderitem是来自表1的idno
当新增一条记录时,表1的allotqty就应该加上表二的quantity
现在如果修改了表二的quantity那么表一的allotqty值就应该是=material.allotqty加上表二修改前的quantity再减去修改后的quantity
我是这样做的
CREATE TRIGGER howedit on poorders
INSTEAD OF UPDATE
AS
BEGIN
update material set allotqty=allotqty+deleted.quantity-inserted.quantity WHERE material.idno=inserted.orderno
END
不对啊!
CREATE TRIGGER howedit on poorders
INSTEAD OF UPDATE
AS
BEGIN
update material set allotqty=allotqty+deleted.quantity-inserted.quantity
from deleted,inserted,material
WHERE material.idno=inserted.orderno and material.idno=deleted.orderno and deleted.orderno=inserted.orderno
END
CREATE TRIGGER howedit on poorders
INSTEAD OF UPDATE
AS
BEGIN
update material set allotqty=allotqty+deleted.quantity-inserted.quantity
from deleted,inserted,material
WHERE material.idno=inserted.orderno and material.idno=deleted.orderno
END
CREATE TRIGGER howedit on poorders
INSTEAD OF UPDATE,insert,delete
AS
BEGIN
update material
set allotqty=allotqty+t1.quantity
from (select ordeitem,sum(quantity) as quantity
from (select ordeitem,quantity from inserted
union all
select ordeitem,-quantity as quantity from deleted ) as t
group by ordeitem
) as t1
WHERE material.idno=t1.orderno
END
注意一次可能修改或者插入多条纪录，应该需要考虑删除纪录的情况
