自动生成剩余编号字符串

原帖地址:

http://community.csdn.net/Expert/topic/3220/3220744.xml?temp=.6029474

问题描述:

insert 主表 select 'Q',0000001,0000100,100,'0000001-0000100'?
union all select 'M',0000001,0000200,200,'0000001-0000200'

insert 子表 select 'Q',0000011,0000030,20
union all select 'Q',0000032,0000050,19
union all select 'Q',0000061,0000080,20
union all select 'M',0000001,0000100,100

如何用触发器在子表中插入每条记录时，更新主表的balance字段呢？

如上想得出的结果是：
id name  strat      end     amount   balance
1   Q    0000001   0000100     41    0000001-0000010,0000031-0000031,0000051-0000060,0000081-0000100   
2   M    0000001   0000200     100   0000101-0000200

----------------------------------------------------------------------------------

--测试数据
CREATE TABLE [dbo].[主表] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [name] [varchar] (50) NULL ,
 [strat] [varchar] (7) NULL ,
 [end] [varchar] (7) NULL ,
 [amount] [int] NULL ,
 [balance] [varchar] (200) NULL
)

CREATE TABLE [dbo].[子表] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [name] [varchar] (50) NULL ,
 [strat] [varchar] (7) NULL ,
 [end] [varchar] (7) NULL ,
 [amount] [int] NULL
)

insert 主表 select 'Q','0000001','0000100',100,'0000001-0000100' 
union all select 'M','0000001','0000200',200,'0000001-0000200'
go

--处理的触发器
create trigger tr_process on 子表
for insert,update,delete
as
select *,[balance]=cast(null as [varchar] (200))
into #t
from 子表 a
where exists(select 1 from inserted where name=a.name)
 or exists(select 1 from deleted where name=a.name)
order by name,[strat]

declare @name varchar(10),@end int,@re varchar(200)
update #t set @re=case @name when name
  then case when @end+1<[strat] then @re+','+right(10000001+@end,7)+'-'+right(9999999+[strat],7)
   else @re+'' end else '' end
 ,@end=[end],@name=name,[balance]=@re

update a set [balance]
 =case when a.[strat]<b.[strat] then a.[strat]+'-'+right(9999999+b.[strat],7) else '' end
  +case when a.[strat]<b.[strat] then b.[balance]
   else case when b.[balance]<>'' then stuff(b.[balance],1,1,'') else '' end end 
  +case when b.[end]<a.[end]
   then case when a.[strat]<b.[strat] or b.[balance]<>''
    then ',' else '' end+right(10000001+b.[end],7)+'-'+a.[end]
   else '' end
 ,[amount]=cast(a.[end] as int)-a.[strat]-b.[amount]+1
from 主表 a,(
 select name,[strat]=min([strat]),[end]=max([end]),[balance]=max([balance]),[amount]=sum([amount])
 from #t
 group by name
)b where a.name=b.name

--处理在子表中被全部删除的数据
if exists(select 1 from deleted a where not exists(select 1 from 子表 where name=a.name))
 update a set amount=cast(a.[end] as int)-a.[strat]+1
  ,[balance]=a.[strat]+'-'+a.[end]
 from 主表 a,(
  select distinct name from deleted a
  where not exists(select 1 from 子表 where name=a.name)
 )b where a.name=b.name
go

--插入子表数据
insert 子表 select 'Q','0000011','0000030',20
union all select 'Q','0000032','0000050',19
union all select 'Q','0000061','0000080',20
union all select 'M','0000051','0000100',50

--更新
update 子表 set name='M'
where ID=1

delete from 子表 where id in(1,3,4)
go

--显示处理结果
select * from 主表
select * from 子表
go

drop table 主表,子表

/*--测试结果

ID  name   strat   end     amount   balance                 
--- ------ ------- ------- -------- -------------------------------
1   Q      0000001 0000100 81       0000001-0000031,0000051-0000100
2   M      0000001 0000200 200      0000001-0000200

（所影响的行数为 2 行）

ID          name   strat   end     amount     
----------- ------ ------- ------- ----------
2           Q      0000032 0000050 19

（所影响的行数为 1 行）
--*/