上面的有错,更正如下
create function dbo.getfun()
returns varchar(20)
as
begin
declare @n varchar(20)
select @n='link_'+convert(varchar(10),isnull(max(cast((right(id,len(id)-5)) as int)),0)+1) from ai
return (@n)
end
create table dbo.ai (id varchar(20) primary key default(dbo.getfun()),ii int)
create function getfun()
returns varchar(20)
as
begin
declare @n varchar(20)
select @n='link_'+cast((isnull(max(id),0)+1) as varchar) from ai
return @n
end
create table ai (id int primary key default(dbo.getfun()),ii int)
用计算列
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Table1
(
id int NOT NULL IDENTITY (1, 1),
mainid AS 'lik_'+str(id)
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
PK_Table1 PRIMARY KEY CLUSTERED
(
mainid
) ON [PRIMARY]
楼主把数据贴出来,如果批量更新。
需要用变量赋值实现如:
declare @int varchar(50)
select * into # from inserted
select @int=isnull(max(replace(id,'lik_','')),122) from ta--这里改为122就行了
update # set id='lik_'+@int,@int=@int+1
create trigger test_ta_tr on ta
instead of insert
as
declare @int varchar(50)
select * into # from inserted
select @int=isnull(max(replace(id,'lik_','')),122) from ta--这里改为122就行了
update # set id='lik_'+@int,@int=@int+1
insert ta
select * from #
--测试
insert ta
select 1,'a'union all--这里1为任何值都不影响id插入的值
select 1,'b'
--定义一个函数
create function roy_fun(@id char(3)=null)
returns char(3)
as
begin
declare @new char(3)
(select @new=replicate('0',(3-len(max(cast(isnull(id,0) as int))+1)))
+cast((max(cast(id as int))+1)as varchar)
from roy)
return(@new)
end
--定义触发器
create trigger roy_tr on roy
for insert
as
update roy
set id=isnull(dbo.roy_fun(null),'001')
where id is null
--测试
insert into roy(name) values('a')
select * from roy
结果:
id name
---- -----
001 a
002 a
insert into ai(ii)values('1')
insert into ai(ii)values('1')
insert into ai(ii)values('1')
insert into ai(ii)values('1')
insert into ai(ii)values('1')
insert into ai(ii)values('1')
select * from ai
--------------------------
id ii
link_1 1
link_2 1
link_3 1
link_4 1
link_5 1
link_6 1