--处理示例--测试表 create table [Order](OrderId varchar(10),paperSizeList varchar(50)) insert [Order] select 'O1','100/200/400' union all select 'O2','100/200/300' union all select 'O3','100/200/300/500'create table [Image](ImageId1 varchar(10),ImageId2 int,printSizeId int) insert [Image] select 'O1',1,1 union all select 'O1',2,2 union all select 'O1',3,4 union all select 'O1',4,2union all select 'O2',1,1 union all select 'O2',2,2 union all select 'O2',3,3union all select 'O3',1,1 union all select 'O3',2,2 union all select 'O3',3,3 union all select 'O3',4,5 go--文件A变为表A create table 表A(col1 int,col2 int) insert 表A select 1,100 union all select 2,200 union all select 3,300 union all select 4,400 union all select 5,500 go--处理的触发器 create trigger tr_update on 表A for update as select a.ImageId1,b.col2,re=cast('' as varchar(8000)) into #t from [Image] a,表A b where a.printSizeId=b.col1 and exists( select 1 from deleted aa,[Image] bb where aa.col1=bb.printSizeId and a.ImageId1=bb.ImageId1) group by a.ImageId1,b.col2 order by a.ImageId1,min(a.ImageId2)declare @a varchar(10),@b varchar(8000) update #t set @b=case @a when ImageId1 then @b+'/'+cast(col2 as varchar) else cast(col2 as varchar) end ,re=@b,@a=ImageId1update a set paperSizeList=b.re from [Order] a join( select ImageId1,re=max(re) from #t group by ImageId1 )b on a.OrderId=b.ImageId1 go--更新测试1(单条记录更新) update 表A set col2=150 where col1=1 --显示结果 select * from [Order]--更新测试1(多条记录更新) update 表A set col2=col2+99 where col1<3 --显示结果 select * from [Order] go--删除测试 drop table [Order],[Image],表A/*--测试结果OrderId paperSizeList ---------- ------------------------- O1 150/200/400 O2 150/200/300 O3 150/200/300/500(所影响的行数为 3 行)OrderId paperSizeList ---------- ------------------------- O1 249/299/400 O2 249/299/300 O3 249/299/300/500(所影响的行数为 3 行) --*/
但java的代码就搞不定了.
------------------------------------------
麻烦老大给写个触发器(就当A是表)
create table [Order](OrderId varchar(10),paperSizeList varchar(50))
insert [Order] select 'O1','100/200/400'
union all select 'O2','100/200/300'
union all select 'O3','100/200/300/500'create table [Image](ImageId1 varchar(10),ImageId2 int,printSizeId int)
insert [Image] select 'O1',1,1
union all select 'O1',2,2
union all select 'O1',3,4
union all select 'O1',4,2union all select 'O2',1,1
union all select 'O2',2,2
union all select 'O2',3,3union all select 'O3',1,1
union all select 'O3',2,2
union all select 'O3',3,3
union all select 'O3',4,5
go--文件A变为表A
create table 表A(col1 int,col2 int)
insert 表A select 1,100
union all select 2,200
union all select 3,300
union all select 4,400
union all select 5,500
go--处理的触发器
create trigger tr_update on 表A
for update
as
select a.ImageId1,b.col2,re=cast('' as varchar(8000))
into #t
from [Image] a,表A b
where a.printSizeId=b.col1 and exists(
select 1 from deleted aa,[Image] bb
where aa.col1=bb.printSizeId and a.ImageId1=bb.ImageId1)
group by a.ImageId1,b.col2
order by a.ImageId1,min(a.ImageId2)declare @a varchar(10),@b varchar(8000)
update #t set
@b=case @a when ImageId1 then @b+'/'+cast(col2 as varchar)
else cast(col2 as varchar) end
,re=@b,@a=ImageId1update a set paperSizeList=b.re
from [Order] a join(
select ImageId1,re=max(re) from #t group by ImageId1
)b on a.OrderId=b.ImageId1
go--更新测试1(单条记录更新)
update 表A set col2=150 where col1=1
--显示结果
select * from [Order]--更新测试1(多条记录更新)
update 表A set col2=col2+99 where col1<3
--显示结果
select * from [Order]
go--删除测试
drop table [Order],[Image],表A/*--测试结果OrderId paperSizeList
---------- -------------------------
O1 150/200/400
O2 150/200/300
O3 150/200/300/500(所影响的行数为 3 行)OrderId paperSizeList
---------- -------------------------
O1 249/299/400
O2 249/299/300
O3 249/299/300/500(所影响的行数为 3 行)
--*/