这些东西通常***留给表示层但是......



如果CustomerNumber具有您指定的格式，那么初学者将需要超过 varchar（10）。



我用过这个表：

  create   table 客户
（
 [dbID]  int  身份 不  null   primary   key ，
 CustomerName  varchar （ 100 ），
 CustomerNumber  varcha r （  20  ）
）

I还创建了一个表，它将为每年提供一个偏移

  create   table  StartYearDetails 
（
 YearEnd  int ，
 MaxId  int  
）

和管理结束年流程的存储过程（这显然可以包含与关闭一年相关的任何其他处理）

  SET   ANSI_NULLS   ON  
 GO  
 SET   QUOTED_IDENTIFIER   ON  
 GO  
 CREATE   PROCEDURE  pCloseY ear 
（
 @ StartYear   int  
）
 AS  
 BEGIN  
 SET   NOCOUNT   ON ; 
 DECLARE   @ MaxId   int  =（ SELECT  MAX（[dbID]） FROM  Customers）
插入 进入 StartYearDetails 值 
（ @ StartYear ，ISNULL（ @ MaxId ， 0 ））

 END  
 GO  

基本上它是捕获在表上分配的最后一个（自动）id的值。它将年份作为参数而不是使用GetDate（），因为年终程序通常在实际实际年终结束后的几天内运行。



起始时运行存储过程的点初始化过程例如

  EXEC  pCloseYear  2016  

给出

 2016 0 

然后我为Customers表

  CREATE   TRIGGER  dbo.Customer_Insert 
 ON 客户
 AFTER  INSERT  
 AS  
 DECLARE   @ offset   int  
 DECLARE   @ yearno   int  

 SELECT   TOP   1   @ offset  = MaxId， @ yearno  = YearEnd  FROM  StartYearDetails  ORDER   BY  YearEnd  DESC  
更新 a 
设置 CustomerNumber = '  MHSIC /' + CAST（ @ yearno   as   varchar ）+ '  / E /' + REPLICATE（'  0'，6-LEN（ cast（a。[dbID]  as   varchar ）））+ CAST（a。[dbID]  -   @ offset   as   varchar ）
来自客户a 
 INNER  上插入了代码关键字> JOIN  b 。[dbID] = b。[dbID] 
 GO  

查询 StartYearDetails 表以计算必要的偏移量以构建派生的 CustomerNumber



我接着测试了这一切...

   -   一些初始数据 
 insert   into 客户（客户名称）值 
（ '  CUSTOMER1 2016'），
（'  CUSTOMER2 2016'），
（'  CUSTOMER3 2016' ），
（'  CUSTOMER4 2016'）

  -   关闭年份 
 EXEC  pCloseYear  2017  

  -   插入更多客户 
插入 进入客户（客户名称） 值 
（'  CUSTOMER5 2017'），
（'  CUSTOMER6 2017'），
（'  CUSTOMER7 2017'），
（'  CUSTOMER8 2017'）

结果：

 1 CUSTOMER1 2016 MHSIC / 2016 / E / 000001 
 2 CUSTOMER2 2016 MHSIC / 2016 / E / 000002 
 3 CUSTOMER3 2016 MHSIC / 2016 / E / 000003 
 4 CUSTOMER4 2016 MHSIC / 2016 / E / 000004 
 5 CUSTOMER5 2017 MHSIC / 2017 / E / 000001 
 6 CUSTOMER6 2017 MHSIC / 2017 / E / 000002 
 7 CUSTOMER7 2017 MHSIC / 2017 / E / 000003 
 8 CUSTOMER8 2017 MHSIC / 2017 / E / 000004 

I want to create an auto generated number year wise like MHSIC/2016/E/000001
But after closing the year it should create as MHSICNG/2017/E/000001

What I have tried:

create table Customers
(
	dbID int identity not null primary key,
	CustomerName varchar(100)
)

create function CustomerNumber (@id int,@year int) 
returns char(5) 
as 
begin 
return 'MHSICNG/' + @year  + 'E' +right('0000' + convert(varchar(10), @id), 4) + 
end


alter table Customers add CustomerNumber varchar(10)
create trigger Customers_insert on Customers 
after insert as 
update 
    Customers 
set 
    Customers.customerNumber = dbo.CustomerNumber(Customers.dbID) 
from 
    Customers 
inner join 
    inserted on Customers                                                                                           .dbID= inserted.dbID


	insert into Customers (CustomerName) values ('jeff') 
select * from Customers

this i have tried but i m not getting result as mentioned above.

These things are usually best left to the presentation layer however...

For the CustomerNumber to have the format you specified you will need more than varchar(10) for starters.

I used this table:

create table Customers
(
	[dbID] int identity not null primary key,
	CustomerName varchar(100),
	CustomerNumber varchar(20)
)

I also created a table that will provide an offset for each year

create table StartYearDetails
(
	YearEnd int,
	MaxId int
)

And a Stored Procedure to manage the "closing year" process (this could obviously contain any other processing related to "closing off a year")

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE pCloseYear
(
	@StartYear int
)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @MaxId int = (SELECT MAX([dbID]) FROM Customers)
	Insert into StartYearDetails values
	(@StartYear, ISNULL(@MaxId,0))

END
GO

Essentially it is capturing the value of the last (automated) id allocated on the table. It takes the year as a parameter rather than using GetDate() as Year End procedures are usually run in the days following the actual physical end of year.

At the starting point running the stored procedure initialises the process e.g.

EXEC pCloseYear 2016

gives

2016	0

I then generated a TRIGGER for the Customers table

CREATE TRIGGER dbo.Customer_Insert
ON Customers
AFTER INSERT 
AS
	DECLARE @offset int 
	DECLARE @yearno int
	
	SELECT TOP 1 @offset = MaxId, @yearno = YearEnd FROM StartYearDetails ORDER BY YearEnd DESC
	UPDATE a 
	set CustomerNumber = 'MHSIC/' + CAST(@yearno as varchar) + '/E/' + REPLICATE('0', 6-LEN(cast(a.[dbID] as varchar))) + CAST(a.[dbID] - @offset as varchar)
	from Customers a
	INNER JOIN inserted b on a.[dbID]=b.[dbID]
GO

which queries the StartYearDetails table to work out the necessary offset to build up the derived CustomerNumber

I then tested it all like this...

-- some initial data
insert into Customers(CustomerName) values
('CUSTOMER1 2016'),
('CUSTOMER2 2016'),
('CUSTOMER3 2016'),
('CUSTOMER4 2016')

-- Close the year
EXEC pCloseYear 2017

-- Insert some more customers
insert into Customers(CustomerName) values
('CUSTOMER5 2017'),
('CUSTOMER6 2017'),
('CUSTOMER7 2017'),
('CUSTOMER8 2017')

Results:

1	CUSTOMER1 2016	MHSIC/2016/E/000001
2	CUSTOMER2 2016	MHSIC/2016/E/000002
3	CUSTOMER3 2016	MHSIC/2016/E/000003
4	CUSTOMER4 2016	MHSIC/2016/E/000004
5	CUSTOMER5 2017	MHSIC/2017/E/000001
6	CUSTOMER6 2017	MHSIC/2017/E/000002
7	CUSTOMER7 2017	MHSIC/2017/E/000003
8	CUSTOMER8 2017	MHSIC/2017/E/000004