根据日期生成单据编号,生成的格式为:P200810100001(即是PyyyyMMdd0000),后面是4位数字,按照该天记录数累加。
例如:
P200810090001
P200810100001
P200810100002
P200810110001
P200810110002
P200810110003
建表SQL:
USE [TestDB]
GO
/****** 对象: Table [dbo].[TableA] 脚本日期: 10/10/2008 09:01:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TableA](
[pkid] [int] IDENTITY(1,1) NOT NULL,
[name] [nchar](10) NULL,
[pid] [varchar](13) NULL,
CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED
(
[pkid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
触发器SQL:
USE [TestDB]
GO
/****** 对象: Trigger [dbo].[Trigger_Insert] 脚本日期: 10/09/2008 17:32:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Trigger_Insert]
ON [dbo].[TableA]
After INSERT
AS
BEGIN
declare @pkid int,@pid varchar(14),@temppid varchar(14)
--从Inserted表中取得主键的自动编号
select @pkid=pkid from Inserted
--获取当前日期格式为"P20081010"
select @pid = 'P' + Convert(varchar(8),GetDate(),112);
--获取最后一个PID
select top 1 @temppid=pid from TableA where pid like @pid+'%' order by pkid desc
if (@temppid is null)
begin
--如果今天没有插入过数据,则初始值为'P200810100001'
set @pid = @pid + '0001'
end
else
begin
--否则从最后一个日期取得编号,并末尾加上1,组成新编号
set @pid = @pid + right(cast(power(10,4) as varchar)+(convert(int,substring(@temppid,10,4))+1),4)
end--更新编号
update TableA set pid=@pid where pkid = @pkid
END
插入测试数据SQL:
INSERT INTO [TestDB].[dbo].[TableA]([name]) VALUES (N'李四')
go
INSERT INTO [TestDB].[dbo].[TableA]([name]) VALUES (N'张三')
go