有一张表,用户在输入数据行的时候要求字段F1的值是自动给定的,为"CD-"+ Year(Date) + "-Seq"的格式,
其中,
"CD-"是定值,
Year(Date)就是当前年份,取两位数,98,99,00,……,
"-Seq"中的Seq是三位序列号,从001开始,如001,002,003……
现在由于不能改动程序,只能从数据库方面改,请教大家如何给这个F1字段自动赋值?
谢谢大家!
其中,
"CD-"是定值,
Year(Date)就是当前年份,取两位数,98,99,00,……,
"-Seq"中的Seq是三位序列号,从001开始,如001,002,003……
现在由于不能改动程序,只能从数据库方面改,请教大家如何给这个F1字段自动赋值?
谢谢大家!
=========
打开表Insert?
假设你的表有id identity,表名为tb的
Id, FormatId, F1 ,F2
Id序号我设了自动加一,FormatId我想他也象这样"SL000001",
当Insert时就加1,FormatId我想他也能自动加一"SL000001","SL000002"...
能用一条sql什么办法实现.最好不要用中间表。有什么好方法?
谢谢!
create table #test
(id int identity,
FormatId as 'SL'+right(10000000+id,6),
F1 varchar(50))
go
insert #test(F1) select '1'
union all select '2'
select * from #testdrop table #test
/*
id FormatId F1
----------- -------------- -----
1 SL000001 1
2 SL000002 2(所影响的行数为 2 行)
*/
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB](
id int identity(1,1),
[班级] varchar(6),
[月份] as 'CD-'+ cast(Year(GETDATE())as varchar(20))+'-'+right('000'+cast(id as varchar(20)),3))
insert [TB]([班级])
select '一年级'
GO--> 查询结果
SELECT * FROM [TB]
--> 删除表格
--DROP TABLE [TB]
[/code]
insert # default values
insert # default valuesselect * from #
/*
Seq F1
----------- ------------
1 CD-10-001
2 CD-10-002
3 CD-10-003
*/drop table #
要是 seq 到100以上了呢?
给个自动生成 序号的存储过程 去研究去if exists(select * from sysobjects where name='usp_yb_autodjh')
drop procedure usp_yb_autodjh
go
create procedure usp_yb_autodjh
@flag int,
@code ut_hth output
AS
/**********
[返回值]
[结果集、排序]
[调用的sp]
[调用实例]
declare @code varchar(32)
exec usp_yb_autodjh 0, @code output
select @code
exec usp_yb_autodjh 1, @code output
select @code
[修改纪录]**********/set nocount on
declare
@maxsjh numeric(12), --已有最大收据号
@lastsjh numeric(12), --增长以后的收据号
@midsjh numeric(12) --中间变量select @midsjh=convert(numeric(12),(convert(char(8),getdate(),112))),@maxsjh=0,@lastsjh=0if @flag=0
begin
select @maxsjh=convert(numeric(12),max(jhdjh)) from SC_SCJHSYK where substring(jhdjh,1,8)=convert(varchar(8),@midsjh) if @maxsjh>0
select @lastsjh= @maxsjh + 1
else
select @lastsjh= @midsjh*10000 + 1 select @code= convert(varchar(14),@lastsjh)
return
end
else if @flag=1
begin
select @maxsjh=convert(numeric(12),max(cgdjh)) from CG_CGSYK where substring(cgdjh,1,8)=convert(varchar(8),@midsjh) if @maxsjh>0
select @lastsjh= @maxsjh + 1
else
select @lastsjh= @midsjh*10000 + 1 select @code= convert(varchar(14),@lastsjh)
return
end
else if @flag=2
begin
select @maxsjh=convert(numeric(12),max(skxh)) from CW_SKMXK where substring(skxh,1,8)=convert(varchar(8),@midsjh) if @maxsjh>0
select @lastsjh= @maxsjh + 1
else
select @lastsjh= @midsjh*10000 + 1 select @code= convert(varchar(14),@lastsjh)
return
end
go
ON dbo.tbA
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Update dbo.tbA set F1=(select 'CD-'+ right(Year(getdate()),2)+ '-' +right('00'+ltrim(id),3))
-- Insert statements for trigger hereEND
GO我希望的结果是,每次增加一条记录的时候,更新该条新加记录的F1字段为"CD-"+ Year(Date) + "-Seq"的格式,但是我现在这样写了以后会将全部记录都更新一遍,应该怎么改才能保证只更新新增加的那条记录的F1字段值呢?
ON dbo.tbA
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Update dbo.tbA set F1=(select 'CD-'''+ right(Year(getdate()),2)+ '-' +right('00'+ltrim(inserted.id),3))
From inserted
where tbA.id=inserted.id
-- Insert statements for trigger hereEND终于搞定了!谢谢大家!
GO