CREATE TRIGGER detail_update ON [dbo].[detail]
FOR UPDATE
AS
UPDATE plan SET plan.use_qty=plan.use_qty-deleted.QTY+inserted.QTY from deleted,inserted WHERE detail.partno = plan.partno
FOR UPDATE
AS
UPDATE plan SET plan.use_qty=plan.use_qty-deleted.QTY+inserted.QTY from deleted,inserted WHERE detail.partno = plan.partno
CREATE TRIGGER 名2 ON dbo.detail
FOR update
ASupdate plan
set use_qty=aa.qyt,use_amount=aa.amount
from inserted aa
where aa.partno=plan.partno
for update
as
declare @plantno,@partno
select @plantno=plantno ,@partno=partno from insert
if update(qty) and update(amount)
begin
update plan set
use_qty=,
use_amout=
where plantno= @plantno,partno=@partno
end
go
是插入的表
FOR update
AS
update [plan] set use_qty=use_qty-tem.qyt,use_amount=use_amount-tem.amount from deleted tem where tem..partno=[plan].partnoupdate [plan] set use_qty=use_qty+tem.qyt,use_amount=use_amount+tem.amount from inserted tem where tem..partno=[plan].partno
FOR update
AS
update [plan] set use_qty=use_qty-a.qyt+b.qyt,use_amount=use_amount-a.amount+b.amount from deleted a,inserted b where a.partno=[plan].partno and a.partno=b.partno
更新這兩個字段的触發器已OK﹐如下﹕CREATE TRIGGER [TRIGGER_data_update] ON [dbo].[taxation_data_save]
FOR UPDATE
AS
if update(qty) or update(amount)--???加這個判斷是不是可以去掉不是更新這兩個字段的動作?
update plan set use_qty = use_qty + a.qty - b.qty,use_amount = use_amount + a.amount - b.amount
from inserted a, deleted b, plan c, list d, detail e
where e.listid = d.listid and d.planno = c.planno
and e.partno = c.partno and a.listid = b.listid
and a.partno = b.partno and a.listid = e.listid
and a.partno = e.partno and a.isplan = 1--可能我沒把情況說明白﹐有多個planno(也就是有多個計划),不同planno(一份計划)可能有相同的partno料號。
--???你們好象都沒有考濾這种情況﹐我參照寫的試過是成功的﹐但這么多條件﹐是不是有些不需要的?
--另a.isplan =1 是指計划內的﹐等于1時才做動作。--???另可以增加﹐刪除該表的記錄時﹐qty和amount的數量也要反應過去﹐我這樣寫一起用時好象有問題﹐請再幫忙看看﹐搞定就結貼。
CREATE TRIGGER [TRIGGER_data_insert] ON [dbo].[taxation_data_save]
FOR INSERT
AS
update tax_plan set use_qty = use_qty + a.qty,use_amount = use_amount + a.amount
from inserted a, tax_plan c, taxaction_list d, taxation_data_save e
where e.listid = d.listid and d.planno = c.planno
and e.partno = c.partno and a.listid = e.listid
and a.partno = e.partno and a.isplan = 1麻煩各位了~~
FOR delete
AS
update tax_plan set use_qty = use_qty + a.qty,use_amount = use_amount + a.amount
from deleted a, tax_plan c, taxaction_list d, taxation_data_save e
where e.listid = d.listid and d.planno = c.planno
and e.partno = c.partno and a.listid = e.listid
and a.partno = e.partno and a.isplan = 1