触发器维护已经用编号/未用编号-白红宇

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

阅读量：4191 次

发布时间：2019-05-26

本文共 3375 字，大约阅读时间需要 11 分钟。

原帖地址:

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))

--触发器

create trigger tr_process on table2

for insert,update,delete

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.终止号

then ''

when b.终止号

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.组合编号

--插入第1条记录

insert table2 select 'A-0000001-0000010','A','0000001'

--显示结果