这样的触发器如何写？

[huhaojie]

A主表： ID , SumValue

ID为键值，SumValue 为明细表的值的总计

B明细表 ID, AID ,Value
ID自增，AID 对应于A表的ID ，Value 为明细值，有正负。

现要求，当B表新增，删除，更新Value时，触发器自动更新A表对应的记录
（规则为：把B表对应的记录的Vaule求和，更新A表的 SumValue 字段）。

[yuejie6666]

go
create trigger tr_ABInsert
on B
for insert
as
declare
@ID int,
@Value int
select @ID=AID,@Value=Value from inserted
update A set SumValue = SumValue+value where ID=@ID
go
create trigger tr_ABDelete
on B
for delete
as
declare
@ID int,
@Value int
select @ID=AID,@Value=Value from deleted
update A set SumValue = SumValue-value where ID=@ID
go
create trigger tr_ABUpdate
on B
for update
as
declare
@ID int,
@newValue int,
@oldValue int
select @ID=AID,@oldValue=Value from deleted
select @newValue-value from inserted
update A set SumValue = SumValue+@newValue-@oldValue where ID=@ID

[pbsql]

前几天刚写了一个类似的，运行还可以，现改了给你：（楼上的没有考虑多条记录的情况）
CREATE TRIGGER tr_insert ON B
FOR INSERT
AS
UPDATE A SET SumValue=SumValue
+(SELECT SUM(Value) FROM inserted WHERE A.ID=inserted.AID)
WHERE exists(SELECT 1 FROM inserted WHERE A.ID=inserted.AID)
GO

CREATE TRIGGER tr_delete ON B
FOR DELETE
AS
UPDATE A SET SumValue=SumValue
-(SELECT SUM(Value) FROM deleted WHERE A.ID=deleted.AID)
WHERE exists(SELECT 1 FROM deleted WHERE A.ID=deleted.AID)
GO

CREATE TRIGGER tr_update ON B
FOR UPDATE
AS
UPDATE A SET SumValue=SumValue
+(SELECT SUM(inserted.Value-deleted.Value) FROM inserted,deleted WHERE A.ID=inserted.AID and inserted.ID=deleted.ID)
WHERE exists(SELECT 1 FROM inserted,deleted WHERE A.ID=inserted.AID and inserted.ID=deleted.ID)
GO