触发器维护已经用编号/未用编号

原帖地址:

http://community.csdn.net/Expert/topic/3298/3298074.xml?temp=.6988336

table1为初始化数据,table2为已用票据

在table2上写触发器，table2每insert,update,or 批量delete时，实时体现tabel1的'已用票号','已用票数', '结余票号','结余票数'的值.

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

--示例

--示例数据
create table table1(序号 int,类别 varchar(10),起始号 char(7),终止号 char(7),总数 int,已用票号 varchar(8000),已用票数 int,结余票数 int,结余票号 varchar(8000),组合编号 varchar(20))
insert table1 select 1,'A','0000001','0000010',10,NULL,0,10,'0000001-0000010','A-0000001-0000010'
union all select 2,'B','0000011','0000020',10,NULL,0,10,'0000011-0000020','B-0000011-0000020'

create table table2(组合编号 varchar(20),类别 varchar(10),票号 char(7))
go

--触发器
create trigger tr_process on table2
for insert,update,delete
as
select id=identity(int,1,1)
,a.组合编号,a.票号
,b.起始号,b.终止号
,已用票号=cast(null as [varchar] (8000))
,结余票号=cast(null as [varchar] (8000))
into #t
from table2 a,table1 b
where a.组合编号=b.组合编号
and (exists(select 1 from inserted where 组合编号=a.组合编号)
or exists(select 1 from deleted where 组合编号=a.组合编号))
order by a.组合编号,a.票号

declare @组合编号 varchar(20),@票号 int
,@已用票号 varchar(8000),@结余票号 varchar(8000)

update #t set
@已用票号=case
when 组合编号=@组合编号
then case
when 票号=@票号+1
then case
when right(@已用票号,1)='-'
then @已用票号+票号
else left(@已用票号,len(@已用票号)-7)+票号
end
else case
when right(@已用票号,1)='-'
then left(@已用票号,len(@已用票号)-1)
else @已用票号 end+','+票号+'-'
end
else 票号+'-'
end,
@结余票号=case
when 组合编号=@组合编号
then case
when 票号=@票号+1
then left(@结余票号,len(@结余票号)-8)
when right(9999999+票号,7)+'-'=right(@结余票号,8)
then left(@结余票号,len(@结余票号)-1)+','
else @结余票号+right(9999999+票号,7)+','
end+right(10000001+票号,7)+'-'
else case
when 起始号=票号
then ''
when cast(起始号 as int)+1=票号
then 起始号+','
else 起始号+'-'+right(9999999+票号,7)+','
end+right(10000001+票号,7)+'-'
end,
已用票号=@已用票号,
结余票号=@结余票号,
@票号=票号,
@组合编号=组合编号

update a set
已用票号=case
when right(b.已用票号,1)='-'
then left(b.已用票号,len(b.已用票号)-1)
else b.已用票号
end,
结余票号=case
when b.终止号=b.票号+1
then left(b.结余票号,len(b.结余票号)-1)
when len(b.结余票号)=8 and b.终止号<stuff(b.结余票号,1,1,'')
then ''
when b.终止号<left(right(b.结余票号,8),7)
then left(b.结余票号,len(b.结余票号)-9)
else b.结余票号+b.终止号
end,
已用票数=c.已用票数,
结余票数=a.总数-c.已用票数
from table1 a,#t b,(
select id=max(id),已用票数=count(*)
from #t
group by 组合编号
)c where a.组合编号=b.组合编号
and b.id=c.id

--处理在子表中被全部删除的数据
if exists(select 1 from deleted a where not exists(select 1 from table2 where 组合编号=a.组合编号))
update a set 已用票号='',已用票数=0,结余票数=a.总数,结余票号=a.起始号+'-'+a.终止号
from table1 a,(
select distinct 组合编号 from deleted a
where not exists(select 1 from table2 where 组合编号=a.组合编号)
)b where a.组合编号=b.组合编号
go

--插入第1条记录
insert table2 select 'A-0000001-0000010','A','0000001'

--显示结果
select 序号,已用票号,已用票数,结余票数,结余票号,组合编号 from table1
go

--插入第2条记录
insert table2 select 'A-0000001-0000010','A','0000002'

--显示结果
select 序号,已用票号,已用票数,结余票数,结余票号,组合编号 from table1
go

--插入第3条记录
insert table2 select 'A-0000001-0000010','A','0000004'

--显示结果
select 序号,已用票号,已用票数,结余票数,结余票号,组合编号 from table1
go

--插入第4条记录
insert table2 select 'A-0000001-0000010','A','0000003'

--显示结果
select 序号,已用票号,已用票数,结余票数,结余票号,组合编号 from table1
go

--修改记录
update table2 set 组合编号='B-0000011-0000020',票号='0000011'
where 组合编号='A-0000001-0000010' and 票号='0000002'

--显示结果
select 序号,已用票号,已用票数,结余票数,结余票号,组合编号 from table1
go

--批量删除:
delete from table2
where 票号 in ('0000001','0000002','0000011')

--显示结果
select 序号,已用票号,已用票数,结余票数,结余票号,组合编号 from table1
go

--删除测试
drop table table1,table2

/*--结果自己看--*/