SqlServer触发器自动生成编号（标识符+年月日+三位流水号）

编号示例：标识符+年月日+三位流水号（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