22,210
社区成员
发帖
与我相关
我的任务
分享
--改下,用事物有问题。。
--1
create trigger checking on tempRecord
for insert
as
begin
declare @type varchar(20),@start int,@end int
declare @checkstart int,@checkend int
declare @startmax int , @endmax int
select @type=type,@start=start,@end=[end] from inserted
select @checkstart=start,@checkend=[end] from [Type]
where id = @type
select @startmax = max(start),@endmax=max([end]) from tempRecord
where type = @type
if @start < @checkstart or @end > @checkend
begin
delete from tempRecord where id = (select id from inserted)
end
else if @start <= @endmax or @end < @start
begin
delete from tempRecord where id = (select id from inserted)
end
end
--1
create trigger checking on tempRecord
for insert
as
begin
begin tran checking
declare @type varchar(20),@start int,@end int
declare @checkstart int,@checkend int
declare @startmax int , @endmax int
select @type=type,@start=start,@end=[end] from inserted
select @checkstart=start,@checkend=[end] from [Type]
where id = @type
select @startmax = max(start),@endmax=max([end]) from tempRecord
where type = @type
if @start < @checkstart or @end > @checkend
begin
rollback tran checking
end
else if @start <= @endmax or @end < @start
begin
rollback tran checking
end
else
begin
commit tran checking
end
end
--2
select id=identity(int,1,1),r.number
into #temp
from Serie t,master..spt_values r
where t.Series = 'av' and r.type='P'
and r.number between t.start and t.[end]
and r.number not in
(select h.number
from Type f,master..spt_values h
where f.SeriesID = t.SeriesID and h.type='P'
and h.number between f.start and f.[end])
select
ltrim(min(number))+'-'+ltrim(max(number)) as number
from #temp
group by number - id
drop table #temp
---------------------
21-24
66-69
101-200