--自已做标识列的例子:--创建得到最大id的函数
create function f_getid()
returns int
as
begin
declare @id int
select @id=max(id) from tb
set @id=isnull(@id,0)+1
return(@id)
end
go--创建表
create table tb(id int default dbo.f_getid() primary key,name varchar(10))
go--创建触发器,在删除表中的记录时,自动更新记录的id
create trigger t_delete on tb
AFTER delete
as
declare @id int,@mid int
select @mid=min(id),@id=@mid-1 from deleted
update tb set id=@id,@id=@id+1 where id>@mid
go--插入记录测试
insert into tb(name) values('张三')
insert into tb(name) values('张四')
insert into tb(name) values('张五')
insert into tb(name) values('张六')
insert into tb(name) values('张七')
insert into tb(name) values('张八')
insert into tb(name) values('张九')
insert into tb(name) values('张十')--显示插入的结果
select * from tb--删除部分记录
delete from tb where name in('张五','张七','张八','张十')--显示删除后的结果
select * from tb--删除环境
drop table tb
drop function f_getid
create function f_getid()
returns int
as
begin
declare @id int
select @id=max(id) from tb
set @id=isnull(@id,0)+1
return(@id)
end
go--创建表
create table tb(id int default dbo.f_getid() primary key,name varchar(10))
go--创建触发器,在删除表中的记录时,自动更新记录的id
create trigger t_delete on tb
AFTER delete
as
declare @id int,@mid int
select @mid=min(id),@id=@mid-1 from deleted
update tb set id=@id,@id=@id+1 where id>@mid
go--插入记录测试
insert into tb(name) values('张三')
insert into tb(name) values('张四')
insert into tb(name) values('张五')
insert into tb(name) values('张六')
insert into tb(name) values('张七')
insert into tb(name) values('张八')
insert into tb(name) values('张九')
insert into tb(name) values('张十')--显示插入的结果
select * from tb--删除部分记录
delete from tb where name in('张五','张七','张八','张十')--显示删除后的结果
select * from tb--删除环境
drop table tb
drop function f_getid
解决方案 »
- 怎样用sql语句查到指定表中所有字段的各种信息
- 关于参数可能为空的存储过程的问题
- 请教:MS SERVER 2008删除大量数据 什么方法最快?
- sql问题,请高手帮忙,万分感谢。急急急!!!
- 就10分了。问一下在update里 - 怎么引
- 急!SQL SERVER 与 PB 的问题
- 有个查询问题速度帮我解决,散分!急用!先谢了.
- 同数据库,不同客户端数据不同的问题
- 为什么SQL Server2005安装一半后,停电了,在安装装就安不上了,怎么解决呀?(在线等.....急)
- 如何把SqlServer选定的多个表用存储过程来创建一个以日期为文件的mdb,并把选定表导入?
- 在Sql-server中怎么建立一个新的Sql-server组,我建了多次没成功请给详细步骤谢谢!
- >请问,SQL 个人版,安装时,到了一大半,出现提示??<
create trigger t_del on B
after delete
as
update B set id=(select sum(1) from B where id<=a.id and a_id=a.a_id)
from B a
go
create table B(id int,a_id int,tel varchar(10))
insert into B
select 1,1,'1111'
union all select 2,1,'2222'
union all select 3,1,'3333'
union all select 1,2,'4444'
union all select 2,3,'5555'go
--创建触发器,重排id
create trigger t_del on B
after delete
as
update B set id=(select sum(1) from B where id<=a.id and a_id=a.a_id)
from B a
go--删除数据
delete from B where id=3 and a_id=1--显示结果
select * from Bgo
--删除测试环境
drop table b/*--测试结果
id a_id tel
----------- ----------- ----------
1 1 1111
2 1 2222
1 2 4444
1 3 5555(所影响的行数为 4 行)
--*/
创建一个这样的函数
create function f_getid()
returns int
as
begin
declare @id int
select @id=max(id) from tb
set @id=isnull(@id,0)+1
return(@id)
endselect @id=max(id) from tb这样会影响速度啊