--方法1.用计算列(这种方式最快,最简单),但要求你的id为标识字段.create table A_Table(id int identity(1,1),AParam varchar(2),ASerialN as AParam+right('00000000'+cast(id as varchar),8))
--例子: create table A_Table(id int identity(1,1),AParam char(2) ,ASerialNO as AParam+right('00000000'+cast(id as varchar),8)) go--插入数据测试 insert into A_Table(AParam) select 'aa' union all select 'bb'--显示结果 select * from A_Table go--删除测试 drop table A_Table/*--测试结果 id AParam ASerialNO ----------- ------ ------------------ 1 aa aa00000001 2 bb bb00000002(所影响的行数为 2 行)--*/
--上面的看错了,应该用触发器来实现--创建生成ASerialNO的触发器 create trigger t_insert on A_Table after insert as update A_Table set ASerialNO=right('00'+cast(b.Code as varchar),2) +right('00000000'+cast(a.id as varchar),8) from A_Table a join inserted i on a.id=i.id join B_Table b on a.AParam=b.AParam go
--上面的错了,改--创建测试的表 create table A_Table(id int identity(1,1),AParam char(2),ASerialNO varchar(10)) create table B_Table(Code decimal(2,0),AParam char(2)) go--创建生成ASerialNO的触发器 create trigger t_insert on A_Table after insert as update A_Table set ASerialNO=right('00'+cast(b.Code as varchar),2) +right('00000000'+cast(a.id as varchar),8) from A_Table a join inserted i on a.id=i.id join B_Table b on a.AParam=b.AParam go--初始B_Table insert into B_Table select 1,'a' union all select 2,'b' union all select 3,'c' go--插入数据测试 insert into A_Table(AParam) values('a') insert into A_Table(AParam) select 'a' union all select 'b' union all select 'c' go--显示结果 select * from A_Table go--删除测试环境 drop table A_Table,b_Table/*--测试结果 id AParam ASerialNO ----------- ------ ---------- 1 a 0100000001 2 a 0100000002 3 b 0200000003 4 c 0300000004(所影响的行数为 4 行) --*/
create table A_Table(id int identity(1,1),AParam char(2)
,ASerialNO as AParam+right('00000000'+cast(id as varchar),8))
go--插入数据测试
insert into A_Table(AParam)
select 'aa'
union all select 'bb'--显示结果
select * from A_Table
go--删除测试
drop table A_Table/*--测试结果
id AParam ASerialNO
----------- ------ ------------------
1 aa aa00000001
2 bb bb00000002(所影响的行数为 2 行)--*/
create trigger t_insert on A_Table
after insert
as
update A_Table set ASerialNO=right('00'+cast(b.Code as varchar),2)
+right('00000000'+cast(a.id as varchar),8)
from A_Table a
join inserted i on a.id=i.id
join B_Table b on a.AParam=b.AParam
go
create table A_Table(id int identity(1,1),AParam char(2),ASerialNO varchar(10))
create table B_Table(Code decimal(2,0),AParam char(2))
go--创建生成ASerialNO的触发器
create trigger t_insert on A_Table
after insert
as
update A_Table set ASerialNO=right('00'+cast(b.Code as varchar),2)
+right('00000000'+cast(a.id as varchar),8)
from A_Table a
join inserted i on a.id=i.id
join B_Table b on a.AParam=b.AParam
go--初始B_Table
insert into B_Table
select 1,'a'
union all select 2,'b'
union all select 3,'c'
go--插入数据测试
insert into A_Table(AParam) values('a')
insert into A_Table(AParam)
select 'a'
union all select 'b'
union all select 'c'
go--显示结果
select * from A_Table
go--删除测试环境
drop table A_Table,b_Table/*--测试结果
id AParam ASerialNO
----------- ------ ----------
1 a 0100000001
2 a 0100000002
3 b 0200000003
4 c 0300000004(所影响的行数为 4 行)
--*/