a 表

aid amax   amin
1    200   50
2    3000  600

b 表

bid   aid  btotal
1     1    50
2     1    120
3     1    200
4     2    600
5     2    3000
6     2    1000
7     2    2000

字段类型都是int，想做一个触发器，让b表insert或者update的时候 判断b表中min(btotal)和max(total)是否等于a表中的amax和amin 通过b.aid=a.aid

就是更新B表的时候自动算出最大值和最小值来更新a表

复制内容到剪贴板

代码:

create trigger aa
on b
for insert,update
as
declare @aid int,@aa int,@min int,@max int
select @aid=aid form inserted
select @aa=btotal form inserted
select @max=max(btotal) from b where aid=@aid
select @min=min(btotal) from b where aid=@aid
if @aa<@max
begin
if @max>(select amax from a where aid=@aid)
begin
update a set amax=@max where aid=@aid
end
else
if @aa>(select amax from a where aid=@aid)
begin
update a set amax=@aa where aid=@aid
end
end
if @aa>@min
begin
if @min<(select amin from a where aid=@aid)
begin
update a set amin=@min where aid=@aid
end
else
if @aa<(select amin from a where aid=@aid)
begin
update a set amin=@aa where aid=@aid
end
end

试了 并不起作用 不知道是为什么
wknight 写的逻辑上是正确的，但我调试了一下不成功啊

按照我的思路，你把我这个最简化，然后去试试，我没调试过，可能哪里有问题，

复制内容到剪贴板

代码:

create trigger i_u on b for insert,update
as
begin
update a set amin=(select min(btotal) from b where a.aid=b.aid)
update a set amax=(select max(btotal) from b where a.aid=b.aid)
end

复制内容到剪贴板

代码:

/*数据字典*/
create table a(
aid int,
amax int,
amin int
)
create table b(
bid int identity primary key,--bid自动增加
aid int,
btotal int
)
/*触发器*/
create  trigger b_in
on b
for insert,update
as
declare @aid int,@max int,@min int
select @aid=aid from inserted
select @max=max(btotal) from b where aid=@aid
select @min=min(btotal) from b where aid=@aid
if (select count(*) from a where aid=@aid)=0
begin
insert into a values(@aid,@max,@min)
end
else
begin
if @max>(select amax from a where aid=@aid)
begin
update a set amax=@max where aid=@aid
end
if @min<(select amin from a where aid=@aid)
begin
update a set amin=@min where aid=@aid
end
end