SQL Trigger 创建按照当日日期的递增表单号

表单编号：C+yyyyMMdd+编号（001开始递增）

如：C20181118001

USE [数据库]
GO
/****** Object:  Trigger [dbo].[表名]    Script Date: 2018/11/21 14:55:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		chenyan
-- Create date: 20180806
-- Description:	form number
-- =============================================
Create TRIGGER [dbo].[Trigger名称]
   ON  [dbo].[表名] 
   AFTER INSERT
AS 
BEGIN
if exists(select * from inserted where new_name is null)
	begin

		DECLARE @CODE varchar(50) --表单表号
		DECLARE @exist_code varchar(50)

		SET @exist_code='C'+convert(varchar(8),GETDATE(),112)
		select @CODE=max(new_name) from [表名]  WITH (NOLOCK) where new_name like @exist_code + '%'

		IF @CODE IS NULL
			BEGIN
				SET @CODE='C'+CONVERT(VARCHAR(8),GETDATE(),112)+'001'
			END
         ELSE
			BEGIN
			    SET @CODE= 'C'+CONVERT(VARCHAR(8),GETDATE(),112)+RIGHT('000'+RTRIM(CONVERT(CHAR(3),CONVERT(INT,RIGHT(@CODE,3))+1)),3)
			END

		UPDATE [表名]
		SET new_name=@CODE
		where new_name is null
	END
END