问一个关联更新触发器的问题
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表 [code]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[/code] 试了 并不起作用 不知道是为什么
wknight 写的逻辑上是正确的，但我调试了一下不成功啊 按照我的思路，你把我这个最简化，然后去试试，我没调试过，可能哪里有问题，:D [code]
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
[/code] [code]/*数据字典*/
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
[/code]
页:
[1]