SET NOCOUNT OFF; GOdeclare @i int set @i = 9SET XACT_ABORT ON; GO BEGIN TRANSACTION; Update Tab set RowNo = RowNo + 1 where RowNO > @i Update Tab set RowNo = RowNo - 1 where RowNO < @i COMMIT TRANSACTION; GO
if object_id('tb')is not null drop table tb go create table tb(RowNo int) insert tb select 1 insert tb select 3 insert tb select 4 insert tb select 9 insert tb select 10 insert tb select 12 if object_id('tri_update')is not null drop trigger tri_update go create trigger tri_update on tb for update as declare @i int ,@j int select @i=rowno from deleted select @j=rowno from inserted if @i=9 and @j=0 update tb set rowno=rowno+1 where rowno<9 and rowno<>0 update tb set rowno=rowno-1 where rowno>9 and rowno<>0 go update tb set rowno=0 where rowno=9 select * from tb /*RowNo ----------- 2 4 5 0 9 11 */
create table b11(RowNo int) insert b11 select 1 insert b11 select 3 insert b11 select 4 insert b11 select 9 insert b11 select 10 insert b11 select 12 create trigger TG_tb on b11 for update as begin declare @i int declare @j int select @i=RowNo from deleted select @j=RowNo from inserted if @i=9 and @j=0 update b11 set RowNo=RowNo+1 where RowNo<9 update b11 set RowNo=RowNo-1 where RowNo>9 endupdate b11 set RowNo=0 where RowNo=9 select * from b11
if object_id('tb') is not null drop table tb go create table tb(Rowno int) go insert into tb select 1 union all select 3 union all select 4 union all select 9 union all select 10 union all select 12 union all select 1000 go if object_id('tr_tb') is not null drop trigger tr_tb go create trigger tr_tb on tb after update as if Exists(select 1 from deleted where Rowno=9) begin begin tran update tb set Rowno=Rowno+1 where Rowno<9 update tb set Rowno=Rowno-1 where Rowno>9 commit end goupdate tb set rowno=0 where rowno=9select * from tb
忘了<>0了if object_id('tb') is not null drop table tb go create table tb(Rowno int) go insert into tb select 1 union all select 3 union all select 4 union all select 9 union all select 10 union all select 12 union all select 1000 go if object_id('tr_tb') is not null drop trigger tr_tb go create trigger tr_tb on tb after update as if Exists(select 1 from deleted where Rowno=9) begin begin tran update tb set Rowno=Rowno+1 where Rowno<9 and Rowno<>0 update tb set Rowno=Rowno-1 where Rowno>9 and Rowno<>0 commit end goupdate tb set rowno=0 where rowno=9select * from tb
-- 创建存储过程来处理 create proc sp_ChangeValue @RowNo int Asupdate TB set RowNo=0 where RowNo = @RowNoupdate TB set RowNo=RowNo+1 where RowNo < @RowNo and RowNo<>0 update TB set RowNo=RowNo-1 where RowNo > @RowNo and RowNo<>0select * from TBgoexec sp_ChangeValue 9 -- 注意: 如果 .. 8, 9, 10 ... 输入的参数的值是9, 要注意 8, 10 这二个值的变化
GOdeclare @i int
set @i = 9SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
Update Tab set RowNo = RowNo + 1 where RowNO > @i
Update Tab set RowNo = RowNo - 1 where RowNO < @i
COMMIT TRANSACTION;
GO
go
create table tb(RowNo int)
insert tb select 1
insert tb select 3
insert tb select 4
insert tb select 9
insert tb select 10
insert tb select 12
if object_id('tri_update')is not null drop trigger tri_update
go
create trigger tri_update on tb
for update
as
declare @i int ,@j int
select @i=rowno from deleted
select @j=rowno from inserted
if @i=9 and @j=0
update tb set rowno=rowno+1 where rowno<9 and rowno<>0
update tb set rowno=rowno-1 where rowno>9 and rowno<>0
go
update tb set rowno=0 where rowno=9
select * from tb
/*RowNo
-----------
2
4
5
0
9
11
*/
insert b11 select 1
insert b11 select 3
insert b11 select 4
insert b11 select 9
insert b11 select 10
insert b11 select 12 create trigger TG_tb on b11
for update
as
begin
declare @i int
declare @j int
select @i=RowNo from deleted
select @j=RowNo from inserted
if @i=9 and @j=0
update b11 set RowNo=RowNo+1 where RowNo<9
update b11 set RowNo=RowNo-1 where RowNo>9
endupdate b11 set RowNo=0 where RowNo=9
select * from b11
if object_id('tb') is not null
drop table tb
go
create table tb(Rowno int)
go
insert into tb
select 1 union all
select 3 union all
select 4 union all
select 9 union all
select 10 union all
select 12 union all
select 1000
go
if object_id('tr_tb') is not null
drop trigger tr_tb
go
create trigger tr_tb
on tb
after update
as
if Exists(select 1 from deleted where Rowno=9)
begin
begin tran
update tb set Rowno=Rowno+1 where Rowno<9
update tb set Rowno=Rowno-1 where Rowno>9
commit
end
goupdate tb set rowno=0 where rowno=9select * from tb
drop table tb
go
create table tb(Rowno int)
go
insert into tb
select 1 union all
select 3 union all
select 4 union all
select 9 union all
select 10 union all
select 12 union all
select 1000
go
if object_id('tr_tb') is not null
drop trigger tr_tb
go
create trigger tr_tb
on tb
after update
as
if Exists(select 1 from deleted where Rowno=9)
begin
begin tran
update tb set Rowno=Rowno+1 where Rowno<9 and Rowno<>0
update tb set Rowno=Rowno-1 where Rowno>9 and Rowno<>0
commit
end
goupdate tb set rowno=0 where rowno=9select * from tb
-- 创建存储过程来处理
create proc sp_ChangeValue
@RowNo int
Asupdate TB set RowNo=0 where RowNo = @RowNoupdate TB set RowNo=RowNo+1 where RowNo < @RowNo and RowNo<>0
update TB set RowNo=RowNo-1 where RowNo > @RowNo and RowNo<>0select * from TBgoexec sp_ChangeValue 9
-- 注意: 如果 .. 8, 9, 10 ... 输入的参数的值是9, 要注意 8, 10 这二个值的变化