34,588
社区成员
发帖
与我相关
我的任务
分享
--自已做标识列的例子:
--创建得到最大id的函数
create function f_getid()
returns int
as
begin
return(select isnull(max(id),0)+1 from tb)
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
/*--测试结果
id name
----------- ----------
1 张三
2 张四
3 张五
4 张六
5 张七
6 张八
7 张九
8 张十
(所影响的行数为 8 行)
id name
----------- ----------
1 张三
2 张四
3 张六
4 张九
(所影响的行数为 4 行)
--*/
create table tb(id varchar(30))
insert tb select '1'
union all select '1.1'
union all select '2'
union all select '2.1'
union all select '2.1.2'
union all select '2.2'
union all select '3'
union all select '3.1'
go
create function dbo.addid(@addid varchar(30),@id varchar(30))
returns varchar(30)
as
begin
declare @newid varchar(30),@mid int
--@addid = 1.2.1 @id = 1.2.2.1
if @addid > @id return @id
-- [1.2.1 => 1.2.] @addid去尾
if charindex('.',@addid)>0
set @addid = left(@addid,len(@addid)+1-charindex('.',reverse(@addid)))
else set @addid = ''
--[1.2.2.1 =>2.1 @newid = 1.2.]@id去头,并将头存入@newid
--前缀不同的直接返回
if @addid <> left(@id,len(@addid)) return @id
select @newid = @addid,@id = stuff(@id,1,len(@addid),'')
--@id去中间部分,并存入@mid [2.1=>.1 @mid = 2]
if charindex('.',@id)> 1
begin
set @mid = left(@id,charindex('.',@id)-1)
set @id = stuff(@id,1,charindex('.',@id)-1,'')
end
else
begin
set @mid = @id
set @id = ''
end
--[@newid = 1.2. + 3 + .1 中间部门加1合并
set @newid = @newid+rtrim(@mid+1)+@id
return @newid
end
go
create trigger t_tb on tb instead of insert
as
begin
--只支持单行插入,如果多行对inserted表使用游标
update a set id = dbo.addid(b.id,a.id) from tb a,inserted b where a.id >= b.id
insert tb select * from inserted
end
go
select * from tb order by id
/*
id
------------------------------
1
1.1
2
2.1
2.1.2
2.2
3
3.1
*/
insert tb(id) select '2.1'
select * from tb order by id
/*
id
------------------------------
1
1.1
2
2.1
2.2
2.2.2
2.3
3
3.1
*/
insert tb(id) select '2'
select * from tb order by id
/*
id
------------------------------
1
1.1
2
3
3.1
3.2
3.2.2
3.3
4
4.1
*/
go
drop table tb
drop function dbo.addid
create table tb(id varchar(30))
insert tb select '1'
union all select '1.1'
union all select '2'
union all select '2.1'
union all select '2.1.2'
union all select '2.2'
union all select '3'
union all select '3.1'
go
create function dbo.addid(@addid varchar(30),@id varchar(30))
returns varchar(30)
as
begin
declare @newid varchar(30),@mid int
--@addid = 1.2.1 @id = 1.2.2.1
if @addid > @id return @id
-- [1.2.1 => 1.2.] @addid去尾
if charindex('.',@addid)>0 set @addid = left(@addid,len(@addid)+1-charindex('.',reverse(@addid)))
--[1.2.2.1 =>2.1 @newid = 1.2.]@id去头,并将头存入@newid
--前缀不同的直接返回
if @addid <> left(@id,len(@addid)) return @id
select @newid = @addid,@id = stuff(@id,1,len(@addid),'')
--@id去中间部分,并存入@mid [2.1=>.1 @mid = 2]
if charindex('.',@id)> 1
begin
set @mid = left(@id,charindex('.',@id)-1)
set @id = stuff(@id,1,charindex('.',@id)-1,'')
end
else
begin
set @mid = @id
set @id = ''
end
--[@newid = 1.2. + 3 + .1 中间部门加1合并
set @newid = @newid+rtrim(@mid+1)+@id
return @newid
end
go
create trigger t_tb on tb instead of insert
as
begin
--只支持单行插入,如果多行对inserted表使用游标
update a set id = dbo.addid(b.id,a.id) from tb a,inserted b where a.id >= b.id
insert tb select * from inserted
end
go
select * from tb order by id
/*
id
------------------------------
1
1.1
2
2.1
2.1.2
2.2
3
3.1
*/
insert tb(id) select '2.1'
select * from tb order by id
/*
id
------------------------------
1
1.1
2
2.1
2.2
2.2.2
2.3
3
3.1
*/
go
drop table tb
drop function dbo.addid
create function dbo.addid(@addid varchar(30),@id varchar(30))
returns varchar(30)
as
begin
declare @newid varchar(30),@mid int
--@addid = 1.2.1 @id = 1.2.2.1
if @addid >= @id return @id
-- [1.2.1 => 1.2.] @addid去尾
if charindex('.',@addid)>0 set @addid = left(@addid,len(@addid)+1-charindex('.',reverse(@addid)))
--[1.2.2.1 =>2.1 @newid = 1.2.]@id去头,并将头存入@newid
select @newid = @addid+'.',@id = stuff(@id,1,len(@addid)+1,'')
--@id去中间部分,并存入@mid [2.1=>.1 @mid = 2]
if charindex('.',@id)> 1
begin
set @mid = left(@id,charindex('.',@id)-1)
set @id = stuff(@id,1,charindex('.',@id)-1)
end
else
begin
set @mid = @id
set @id = ''
end
--[@newid = 1.2. + 3 + .1 中间部门加1合并
set @newid = @newid+rtrim(@mid+1)+@id
return @newid
end
go
--例子,可以将UPDATE 语句放入表的INSERT 触发器。
declare @id varchar(30)
insert tb(id) select @id
update tb set id = dbo.addid(@id,id) where id > @id