类型：转载 责任编辑：asp.net 日期：2007/05/23
热门软件下载：
我有這樣一個表
序號 姓名 電話號碼
1 aa 1233445
2 bb 89202
3 cc 9380
4 dd 380200
其中序號是自增的,我在表里進行了插入及刪除的功能,結果"序號"就斷斷續續的,如變成了"1,3,4,5,8",不能連續顯示,請問怎麼做才能在插入和刪除后,序號會再次自動排序成"1,2,3,4,5"呢,多多請教了!!
网友回答：
删除 序號 字段,再重新添加回去就行了.
给你一个例子吧：做两个触发器
ALTER trigger 自增_简答题delete
on 简答题
for delete
as
declare @id int,@cnt int
select @cnt=count(*) from deleted
select @id=max(记录号) from deleted
begin
update 简答题 set 记录号=记录号-@cnt,@id=@id+1 where 记录号>@id
end
ALTER trigger 自增_简答题insert
on 简答题
after insert
as
if not exists(select * from 简答题)
begin
update 简答题 set 记录号=1
end
else
begin
update 简答题 set 记录号=(select count(*) from 简答题) where 简答题.题目 in (select
题目 from inserted)
end
--记录号为所要实现自增的列
create table t1(a int,b varchar(10),c varchar(10))
create trigger tg_t1 on t1
for insert
as
declare @max int,
@b varchar(10)
select @b=b from inserted
select @max=count(@@RowCount)from t1
update t1 set a=@max where b=@b
---------------------------
--test
insert into t1 values(5,abc,desfff)
可以把SET IDENTITY_INSERT设置为On
下例创建一个含有标识列的表，并显示如何使用 SET IDENTITY_INSERT 设置填充由 DELETE 语句导致的标识值中的空隙。
-- Create products table.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
-- Inserting values into products table.
INSERT INTO products (product) VALUES (screwdriver)
INSERT INTO products (product) VALUES (hammer)
INSERT INTO products (product) VALUES (saw)
INSERT INTO products (product) VALUES (shovel)
GO
-- Create a gap in the identity values.
DELETE products
WHERE product = saw
GO
SELECT *
FROM products
GO
-- Attempt to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO products (id, product) VALUES(3, garden shovel)
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO
-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, garden shovel).
GO
SELECT *
FROM products
GO
-- Drop products table.
DROP TABLE products
GO