排序也没用. 我 select * from tb 这样查询 是乱的 select * from tb order by id 样加 order by 是对的 就是说他默认 是不按 id 排序了.
--Step01. CREATE TABLE #temp(id INT IDENTITY(1,1),descrp CHAR(2))INSERT INTO #temp SELECT 'AA' UNION ALL SELECT 'BB' UNION ALL SELECT 'CC' UNION ALL SELECT 'DD' go--Step02. SELECT * FROM #temp go--Step03. SET IDENTITY_INSERT #temp ON DELETE FROM #temp WHERE id=2--Step04. SELECT * INTO #temp00 FROM #temp WHERE id >2 DELETE FROM #temp WHERE id > 2 INSERT #temp(id,descrp) SELECT id - 1 ,descrp FROM #temp00 --Step05. DECLARE @i INT SELECT @i = COUNT(1) FROM #temp SET IDENTITY_INSERT #temp OFF DBCC CHECKIDENT ('#temp',RESEED,@i) go --Step06. INSERT INTO #temp VALUES('ee') SELECT * FROM #temp --Step07. DROP TABLE #temp DROP TABLE #temp00
这是触发器的例子 create table ta(id int,col char(1)) gocreate trigger trig on ta for delete as begin update a set id = id - 1 from ta a where id >(select id from deleted) end go insert ta select 1,'1' insert ta select 2,'2' insert ta select 3,'3' insert ta select 4,'4' insert ta select 5,'5' select * from ta delete from ta where id = 2select * from tadrop table ta /*id col ----------- ---- 1 1 2 2 3 3 4 4 5 5(所影响的行数为 5 行) (所影响的行数为 1 行)id col ----------- ---- 1 1 2 3 3 4 4 5(所影响的行数为 4 行) */
那你就排序后插入,这样:select * into # from tbtruncate table tbinsert into tb(name) select name from # order by iddrop table #
这样查询 是乱的
select * from tb order by id
样加 order by 是对的 就是说他默认 是不按 id 排序了.
--Step01.
CREATE TABLE #temp(id INT IDENTITY(1,1),descrp CHAR(2))INSERT INTO #temp
SELECT 'AA'
UNION ALL
SELECT 'BB'
UNION ALL
SELECT 'CC'
UNION ALL
SELECT 'DD'
go--Step02.
SELECT * FROM #temp
go--Step03.
SET IDENTITY_INSERT #temp ON
DELETE FROM #temp WHERE id=2--Step04.
SELECT * INTO #temp00 FROM #temp WHERE id >2
DELETE FROM #temp WHERE id > 2
INSERT #temp(id,descrp) SELECT id - 1 ,descrp FROM #temp00
--Step05.
DECLARE @i INT
SELECT @i = COUNT(1) FROM #temp
SET IDENTITY_INSERT #temp OFF
DBCC CHECKIDENT ('#temp',RESEED,@i)
go
--Step06.
INSERT INTO #temp VALUES('ee')
SELECT * FROM #temp
--Step07.
DROP TABLE #temp
DROP TABLE #temp00
id col
----------- ----
1 1
2 2
3 3
4 4
5 5(所影响的行数为 5 行)
(所影响的行数为 1 行)id col
----------- ----
1 1
2 3
3 4
4 5(所影响的行数为 4 行)
create table ta(id int,col char(1))
gocreate trigger trig
on ta
for delete
as
begin
update a
set id = id - 1
from ta a
where id >(select id from deleted)
end
go
insert ta select 1,'1'
insert ta select 2,'2'
insert ta select 3,'3'
insert ta select 4,'4'
insert ta select 5,'5'
select * from ta
delete from ta where id = 2select * from tadrop table ta
/*id col
----------- ----
1 1
2 2
3 3
4 4
5 5(所影响的行数为 5 行)
(所影响的行数为 1 行)id col
----------- ----
1 1
2 3
3 4
4 5(所影响的行数为 4 行)
*/