求触发器？

[jannock]

有两张表 一张是概要表A 另一张是详细表B
A 表
a_bh a_person a_sumjine (三个字段 单据编号、经手人 总金额)
1111 aaaaaa 2600
2222 aaaaaa 1200

B表
b_bh b_goods b_sl b_dj b_jine(5个字段，单据编号，商品名称，商品数量，商品单价，商品金额）
1111 商品A 100 12 1200
1111 商品B 50 4 200
1111 商品C 120 10 1200
2222 商品B 150 4 600
2222 商品C 60 10 600

先求一触发器 当B 表中 插入、更新（b_jine）、删除时，对应A 表中的 A_SUNJINE 字段更新新的值

CREATE TRIGGER updatejine ON b
FOR INSERT, UPDATE, DELETE
AS
update a
set a_sumjine=tb.sumjine
from (select sum(b_jine) as sumjine,b_bh from b group by b_bh) as tb
where tb.b_bh=a_bh
结果更新了所有记录
我只想更新 插入、删除、更新的记录中的b_bh 对应的 A表中的 a_sumjine 字段

由于初学，向大家请教学习如何修改成我需要的。

[caoyang0299]

给你个例子参考下
create trigger tri_fchuku on t_BOS200000089entry
for insert, update, delete
as
if not exists ( select 1
from inserted )
begin
declare @did int
set @did = ( select finterid
from icstockbill
where fbillno in ( select fbillno
from t_BOS200000089
where fid in ( select fid
from deleted ) )
)
delete from icstockbillentry
where finterid = @did
and fentryid = ( select fentryid
from deleted
)
end

else
if not exists ( select 1
from deleted )
begin
declare @iid int
set @iid = ( select max(finterid)
from icstockbill
)
insert into icstockbillentry
(
fbrno ,
finterid ,
fentryid ,
fitemid ,
funitid ,
fauxqty ,
fconsignprice ,
fconsignamount ,
fdcstockid
)
select 0 ,
@iid ,
t1.fentryid ,
fbase1 as '物料' ,
it.funitid as '单位' ,
fdecimal18 as '数量' ,
famount ,
famount1 ,
fbase9 as '仓库'
from inserted t1 ,
t_icitem it
where t1.fbase1 = it.fitemid
end
else
update icstockbillentry
set fbrno = 0 ,
fentryid = t1.fentryid ,
fitemid = it.funitid ,
fauxqty = fdecimal18 ,
fconsignprice = famount ,
fconsignamount = famount1 ,
fdcstockid = fbase9
from inserted t1 ,
t_icitem it
where t1.fbase1 = it.fitemid
go

[jannock]

晕 太长了 看不懂呀 能解释一下吗？

[xuam]

CREATE TRIGGER updatejine ON b  
FOR INSERT, UPDATE, DELETE  
AS
update a
set a_sumjine=tb.sumjine
from (select sum(b_jine) as sumjine,b_bh from b ,inserted  where b.bh= inserted.bh group by b_bh) as tb
where tb.b_bh=a_bh

[xuam]

CREATE TRIGGER updatejine ON b  
FOR INSERT, UPDATE, DELETE  
AS
update a
set a_sumjine=tb.sumjine
from (select sum(b_jine) as sumjine,b_bh from b ,inserted  where b.b_bh= inserted.b_bh group by b_bh) as tb
where tb.b_bh=a_bh and inserted.b_bh = a.a_bh

[lamudaisy]

[Quote=引用 1 楼 的回复:]

SQL code
CREATE TRIGGER updatejine ON b
FOR INSERT, UPDATE, DELETE
AS
update a
set a_sumjine=tb.sumjine
from (select sum(b_jine) as sumjine,b_bh from b ,inserted where b.bh= inserted.bh group b……
[/Quote]
这种方法处理不到Delete的数据。需要把Delete的分出来重新创建一下新的触发器。

CREATE TRIGGER updatejine ON b  
FOR  DELETE  
AS
update a
set a_sumjine=tb.sumjine
from (select sum(b_jine) as sumjine,b_bh from b ,Deleted  where b.bh= Deleted.bh group by b_bh) as tb
where tb.b_bh=a_bh

楼主的这种需求最好不要用触发器处理

[jannock]

请问 顺其自然 最好怎么做呀
我是刚学的