最好用触发器,如果简单的表可以这样完成create table Pr_Jc
(
id int identity,
Pr_JcNum as right(Convert(varchar(4),year(getdate())),2) + REPLICATE('0',(4-len(CONVERT(varchar(4),id))))+CONVERT(varchar(4),id) + 'S',
Name varchar(20)
)insert into pr_jc(name) values('test')select * from pr_jc
(
id int identity,
Pr_JcNum as right(Convert(varchar(4),year(getdate())),2) + REPLICATE('0',(4-len(CONVERT(varchar(4),id))))+CONVERT(varchar(4),id) + 'S',
Name varchar(20)
)insert into pr_jc(name) values('test')select * from pr_jc
这样的长度是8000
如果我只需要长度是8位呢
(
id int identity,
Pr_JcNum varchar(8),
Name varchar(20)
)CREATE TRIGGER Pr_Jc_Insert ON Pr_Jc
FOR INSERT
AS
UPDATE Pr_Jc
SET Pr_JcNum =(SELECT right(Convert(varchar(4),year(getdate())),2) + REPLICATE('0',(4-len(CONVERT(varchar(4),i.id))))+CONVERT(varchar(4),i.id) + 'S')
FROM Pr_Jc INNER JOIN inserted i
ON i.id=Pr_Jc.idinsert into pr_jc(name) values('test')select * from pr_jc