求触发器？

Question

有两张表  一张是概要表A   另一张是详细表B
 A 表
 a_bh    a_person    a_sumjine  (三个字段  单据编号、经手人  总金额)
1111     aaaaaa        2600
2222     aaaaaa        1200B表 
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 字段由于初学，向大家请教学习如何修改成我需要的。
 

Answer 1

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

Answer 2

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

Answer 3

这种方法处理不到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
楼主的这种需求最好不要用触发器处理

Answer 4

给你个例子参考下
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