Создание имени на основе идентификатора и текущего года с использованием SQL Server

Я пытаюсь сгенерировать значение для столбца ProjektNummer (Имя) на основе значения ID (столбец первичного ключа.

Так, например, когда ID равно 142, тогда ProjektNummer должно быть 19142. 19 обозначает текущий год, а 142 - значение его ID.

Теперь, когда год меняется, часть идентификатора в значении столбца имени проекта должна быть перезапущена с нуля, в то время как фактический идентификатор должен следовать за идентификатором и увеличиваться на 1, как обычно.

Итак, если последняя запись в 2019 году имеет ID = 164, первая запись в 2020 году должна быть:

ID: 165 ProjektNummer: 20001

.. и вторым рекордом в 2020 году будет:

ID: 166 ProjektNummer: 20002

Один из способов добиться этого - создать новое представление vMaxLastId и сохранить максимальное значение ID для соответствующего года. Эта запись будет использоваться в качестве ссылки в другом триггере.

Итак, если первая запись в 2020 году имеет ID: 165, другой триггер вычитает значение (164) для 2019 года (которое хранится в представлении) из значения ID после вставки новой записи в table1. 'с помощью второго триггера. (это нужно сделать перед тем, как вставить tho)

165-164=1
166-164=2
167-164=3
....

Когда год изменится с 2019 на 2020, максимальный идентификатор и год в 2020 будут добавлены в виде новой записи в представлении. Я реализовал это, но с этим подходом связано множество проблем.

Пожалуйста, предложите мне любой возможный способ решения этой проблемы. Я готов внести любые изменения в БД, запрос, триггер или всю концепцию.


sql database sql-server triggers database-trigger
person Junaid    schedule 06.12.2019    source источник
comment
У вас может быть отдельная таблица с максимальным идентификатором за год и обновлять ее с помощью триггеров. Это может быть неэффективным решением, если у вас большой объем вставок.   -  person Gordon Linoff    schedule 06.12.2019

Ответы (2)


arrow_upward
0
arrow_downward

Первая мысль, которая пришла мне в голову, заключалась в создании _ 1_ для создания ID части значения. Поскольку в отношении нумерации нет никаких сложных правил, подойдет именно это.

CREATE SEQUENCE dbo.ProjektID;

Это даст вам bigint. Чтобы использовать его, CONCAT это значение текущего года и поместите нули заполнения в значение последовательности. Вы можете установить это значение по умолчанию для столбца.

CONCAT( YEAR( GETDATE() ) % 100 , RIGHT( CONCAT('000', NEXT VALUE FOR dbo.ProjektID), 3))

Затем запланируйте запуск задания в полночь первого числа года, чтобы перезапустить последовательность.

ALTER SEQUENCE dbo.ProjektID
  RESTART;

Я думаю, что это покроет ваши требования.

person Eric Brandt    schedule 06.12.2019
comment
Спасибо за Ваш ответ. Я это реализовал. Есть ли способ проверить эту работу? - person Junaid; 07.12.2019

arrow_upward
0
arrow_downward

Самым прагматичным решением, которое я мог придумать, является использование триггера INSTEAD OF для этой конкретной основной таблицы.

Моя тестовая таблица:

CREATE TABLE dbo.MyTestProject
(
  id            INT          IDENTITY(1, 1) NOT NULL PRIMARY KEY
, dateInserted  DATETIME2(7) NOT NULL
, projectNumber INT          NULL
);

И пример триггера:

CREATE TRIGGER dbo.triMyTestProject
ON dbo.MyTestProject
INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE
    @maxYear      INT
  , @maxNumber    INT
  , @startNumber  INT
  , @dateInserted DATETIME2;

  SELECT
       @dateInserted = dateInserted
  FROM Inserted;

  SET @startNumber = CAST(FORMAT(@dateInserted, 'yy') AS INT) * 1000 + 1;

  SELECT
       @maxNumber = MAX(projectNumber)
     , @maxYear   = ISNULL(MAX(YEAR(dateInserted)), YEAR(@dateInserted))
  FROM dbo.MyTestProject;

  INSERT INTO dbo.MyTestProject
    (
      dateInserted
    , projectNumber
    )
  VALUES
    (
      @dateInserted, IIF(YEAR(@dateInserted) > @maxYear OR @maxNumber IS NULL, @startNumber, @maxNumber + 1)
    );
END;

Добавьте тестовые данные:

INSERT INTO dbo.MyTestProject (dateInserted) VALUES (SYSDATETIME());
INSERT INTO dbo.MyTestProject (dateInserted) VALUES (SYSDATETIME());
INSERT INTO dbo.MyTestProject (dateInserted) VALUES (SYSDATETIME());
INSERT INTO dbo.MyTestProject (dateInserted) VALUES ('20200102');
INSERT INTO dbo.MyTestProject (dateInserted) VALUES ('20200202');

Результат испытаний:

SELECT * FROM dbo.MyTestProject;


id          dateInserted                projectNumber
----------- --------------------------- -------------
1           2019-12-06 17:27:58.8813628 19001
2           2019-12-06 17:27:58.8833638 19002
3           2019-12-06 17:27:58.8883448 19003
4           2020-01-02 00:00:00.0000000 20001
5           2020-02-02 00:00:00.0000000 20002
person Thailo    schedule 06.12.2019