--自动生成订单号,以时间日期为前缀,后加自动编号
--数据库,存储过程之触发器自动生成订单号
--Create By Lynn
Create TRIGGER [InsertBookList] ON [dbo].[BookList]
FOR Insert
AS
declare @date1 datetime
declare @tempDateStr nvarchar(11)
set @date1 = getdate()
set @tempDateStr = left(Convert(Varchar(4),Year(@date1)),2) + CONVERT(VARCHAR(8),@date1,12)
--print @tempDateStr
--
declare @maxBookOrderNumber bigint
declare @maxListID bigint
select @maxBookOrderNumber=max(BookOrderNumber) from [BookList]
declare @tempHead nvarchar(8)
set @tempHead = left(Convert(Varchar(11),@maxBookOrderNumber),8)
--print @tempHead
--print @tempDateStr
if @maxBookOrderNumber is null or @maxBookOrderNumber='19000101001' or @tempHead<> @tempDateStr
begin
set @maxBookOrderNumber = @tempDateStr + '001'
--print @maxBookOrderNumber
end
else
begin
declare @tempBibInt bigint
set @tempBibInt = convert(bigint,@maxBookOrderNumber) + 1
set @maxBookOrderNumber = convert(Varchar(11),@tempBibInt)
--print @tempBibInt
end
--print @maxBookOrderNumber
select @maxListID= BookID from inserted
Update [BookList] set BookOrderNumber = @maxBookOrderNumber where BookID = @maxListID