manishss Ответов: 1

Как создать алфавитно-цифровой автогенерированный столбец в таблице SQL server без использования первичного ключа.

Я хочу создать автоматически сгенерированное число по годам, например MHSIC/2016/E/000001
Но после закрытия года он должен быть создан как MHSICNG / 2017/E / 000001

Что я уже пробовал:

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


это я уже пробовал, но не получаю результата, как упоминалось выше.

1 Ответов

Рейтинг:
1

CHill60

Однако эти вещи обычно лучше оставить на уровне презентации...

К тому же идентификатору плательщика, чтобы иметь указанном формате вам нужно больше, чем varchar(10) для начала.

Я использовал эту таблицу:

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

Я также создал таблицу, которая будет предоставлять смещение для каждого года
create table StartYearDetails
(
	YearEnd int,
	MaxId int
)

И хранимая процедура для управления процессом "закрытия Года" (очевидно, она может содержать любую другую обработку, связанную с " закрытием года")
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
По сути, это захват значения последнего (автоматизированного) идентификатора, выделенного в таблице. Он принимает год в качестве параметра, а не использует GetDate (), поскольку процедуры конца года обычно выполняются в дни, следующие за фактическим физическим концом года.

В начальной точке выполнение хранимой процедуры инициализирует процесс, например
EXEC pCloseYear 2016

дает 
2016	0
Затем я сгенерировал триггер для таблицы Customers 
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
который запрашивает StartYearDetails таблица для выработки необходимого смещения для построения производной CustomerNumber

Затем я проверил все это вот так...
-- 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')

Результаты:
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