＋设为首页 ＋添加到收藏夹
如何設置一個字段﹐為自動生成的流水號﹐在新增時﹐自動出現﹐其值為20030813001...20030813002...與日期相關的﹐請講得詳細點﹐TKS
建自定义函数,得到最新的流水号,
并设置为字段的默认值就行了.
写一个函数，放在默认值里：
create function getNumber()
returns char(11)
as
return (select convert(char(8),getdate(),112)+right(00+cast(isnull(max(字段),0)+1 as varchar(3)),3) from 表)
go
你在你的表里的那个字段的默认值里写：
dbo.getNumber()
建一个表
create table aa
(
id char(11)
)
添加一个触发器
CREATE TRIGGER [aa_add] ON dbo.aa
FOR INSERT
as
declare @code char(8)
declare @year varchar(4)
declare @month varchar(2)
declare @day varchar(2)
set @year = cast(year(getdate()) as varchar(4))
set @month = cast(month(getdate()) as varchar(2))
set @day = cast(day(getdate()) as varchar(2))
if(len(@month) = 1)
set @month = 0 + @month
if(len(@day) = 1)
set @day = 0 + @day
set @code = @year + @month + @day
update aa set id = (select id =
case when max(id) is null then @code+001
when len(cast((cast(max(substring(id,9,3)) as int) + 1) as char)) = 1 then @code + 00 + cast((cast(max(substring(id,9,3)) as int) + 1) as varchar)
when len(cast((cast(max(substring(id,9,3)) as int) + 1) as char)) = 2 then @code + 0 + cast((cast(max(substring(id,9,3)) as int) + 1) as varchar)
when len(cast((cast(max(substring(id,9,3)) as int) + 1) as char)) = 3 then @code + cast((cast(max(substring(id,9,3)) as int) + 1) as varchar)
end
from aa where id like @code+%)
where id = (select id from inserted)
程序里的插入语句这样写
insert into aa values(00000000000)
select * from aa
ok了！
大力的好。
写一个函数，放在默认值里：
create function getNumber()
returns char(11)
as
return (select convert(char(8),getdate(),112)+right(00+cast(isnull(max(字段),0)+1 as varchar(3)),3) from 表)
go
你在你的表里的那个字段的默认值里写：
dbo.getNumber()
将触发器中
declare @code char(8)
declare @year varchar(4)
declare @month varchar(2)
declare @day varchar(2)
set @year = cast(year(getdate()) as varchar(4))
set @month = cast(month(getdate()) as varchar(2))
set @day = cast(day(getdate()) as varchar(2))
if(len(@month) = 1)
set @month = 0 + @month
if(len(@day) = 1)
set @day = 0 + @day
set @code = @year + @month + @day
用大力的
declare @code char(8)
set @code = convert(char(8),getdate(),112)
代替就更好了。
其实大力得方法更好，但是我在创建函数时出错，提示在函数内部不正确的使用了getdate，请大力修改一下如何？
CREATE TRIGGER [aa_add] ON dbo.aa
FOR INSERT
as
declare @date varchar(8)
set @date = convert(char(8),getdate(),112)
update aa set id =
(select right(00+ cast(cast(substring(isnull(max(id),000000000), 9, 3) as int) + 1) as char, 3)
from aa where id like @code+%)
go
