82.触发器字段维护已用

川长思鸟来 2024-02-19 18:03 90阅读 0赞

--根据 table2 表中的记录变化情况,自动修改 table1 表的内容
    
    --示例
    
    --示例数据
    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.票号
    		then left(b.结余票号,len(b.结余票号)-1)
    		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
    
    /*--结果自己看--*/