编号示例:标识符+年月日+三位流水号(PEPS20211011001)
CREATE TRIGGER Order_Review_Stream
ON reviewnews
After INSERT
AS
BEGIN
declare @review_id int,@review_stream varchar(15),@temppid varchar(15)
--从Inserted表中取得主键的自动编号
select @review_id=review_id from Inserted
--获取当前日期格式为"PEPS20211011"
select @review_stream = 'PEPS'+Convert(varchar(8),GetDate(),112);
--获取最后一个review_id
select top 1 @temppid=review_stream from reviewnews where review_stream like @review_stream+'%' order by review_id desc
if (@temppid is null)
begin
--如果今天没有插入过数据,则初始值为'PEPS20211011001'
set @review_stream = @review_stream + '001'
end
else
begin
--否则从最后一个日期取得编号,并末尾加上1,组成新编号
set @review_stream = @review_stream + right(cast(power(10,3) as varchar)+(convert(int,substring(@temppid,14,3))+1),3)
end
--更新编号
update reviewnews set review_stream=@review_stream where review_id = @review_id
END