--使用一个序号管理表，管理每天的序号
create table IndexManager
(
  [Date] varchar(8),
  px int 
)

create table Persons(
 id int identity(1,1),
 P_Date datetime,
 P_Name nvarchar(10),
 P_NO varchar(11)

)  



create trigger tr_Persons_Insert on Persons 
for insert
AS
begin
  declare @id int
  declare @px int
  declare @today varchar(8)
  select @today =replace(CONVERT (varchar(10),GETDATE(),120),'-','')
  
  IF EXISTS(select 1 from IndexManager where [Date]=@today)
      begin
         update IndexManager set px=px+1 where [Date]=@today   --如果当天记录存在，则先更新，后取出
         select @px=px from IndexManager where [Date]=@today
      end
  else
    begin
     insert into IndexManager([Date],px) values(@today,1) --如果当天记录不存在，则先插入，后取出
     select @px=px from IndexManager where [Date]=@today
    end
    
   select @id=id from inserted
   update Persons set P_NO=@today + right('000' + cast(@px as varchar),3) where id=@id
  
end

insert into Persons(P_Date,P_Name)values(getdate(),'张三1' )
select * from Persons
insert into Persons(P_Date,P_Name)values(getdate(),'张三2' )
select * from Persons