Сохранение текущего года из одной таблицы в другую при изменении года с помощью триггера

У меня есть таблица с именем «Таблица1», в которой есть столбец первичного ключа с именем ID и столбец с именем CreatedAt типа date.

У меня есть другая таблица с именем «MaxLastId», в которой я хочу сохранить максимальное значение ID «Table1» при изменении года с использованием триггера AFTER INSERT.

Например, если данные Table1:

ID    | CreatedAt
16    | 31-Dec-2019
17    | 01-Jan-2020

... Данные таблицы MaxLastId должны стать:

maxId

16

Поскольку 16 - это значение ID последней строки, вставленной в 2019 году. То же самое должно произойти для 2020, 2021 года и так далее.

Таблица MaxLastId должна содержать только одну строку. Через год будет обновляться единственная строка в MaxLastId.

Что я пробовал до сих пор:

CREATE TRIGGER [dbo].[SaveLastYearMaxId]
ON Table1
AFTER INSERT
AS
BEGIN
    IF NOT EXISTS (SELECT * FROM MaxLastId)
    BEGIN
         IF (SELECT DATEDIFF(yy,(SELECT CreatedOn from inserted), (SELECT MAX(id) FROM Table1 WHERE id <(SELECT MAX(id) FROM Table1)))) = 1
         -- ( max - second max as first max is 'inserted' already)
         BEGIN
            INSERT INTO MaxLastId SELECT id from inserted
         END    
    END     
ELSE
    BEGIN       
        IF (SELECT DATEDIFF(yy,(SELECT CreatedOn from inserted), (SELECT CreatedOn from Table1 where id = (Select [maxId] from MaxLastId)))) = 1
            BEGIN
                UPDATE MaxLastId SET [maxId] = (SELECT id from inserted)
            END 
    END 
END

У меня есть вопросы:

  • Сработает ли этот триггер? Если да, можно ли лучше написать этот триггер?
  • Есть ли лучший подход к решению этой проблемы?

Я не очень хорошо разбираюсь в триггерах SQL.


sql sql-server triggers sql-update sql-date-functions
person Junaid    schedule 05.12.2019    source источник
comment
Это не сработает из года в год, если вы каким-то образом не усекаете таблицу. Я бы посоветовал хранить максимальный идентификатор в год.   -  person Gordon Linoff    schedule 05.12.2019
comment
Спасибо за ответ и ответ. Не могли бы вы подсказать мне, что, если я усечу, а затем добавлю новую строку вместо ОБНОВЛЕНИЯ? Мне нужна только одна запись в этой таблице. Что ты посоветуешь?   -  person Junaid    schedule 05.12.2019
comment
Разве не было бы значительно проще запланировать какое-то задание, которое будет запускаться на 1 января в 12 часов утра, чтобы записать эту запись?   -  person James Z    schedule 05.12.2019
comment
Раньше я не писал плановых заданий. Не могли бы вы подробнее рассказать об этом?   -  person Junaid    schedule 05.12.2019

Ответы (2)


arrow_upward
1
arrow_downward

Самым важным является то, что inserted может иметь несколько строк, поэтому ваш триггер неверен.

Я бы поместил год в справочную таблицу:

create table maxlastid (
    year int unique,
    lastid int
);

Я пытаюсь придумать способ сделать это без блокировки всей таблицы и сериализации вставок. Это сложно в многопоточной среде. Идея такая:

  1. Попытайтесь вставить максимальный идентификатор в таблицу.
  2. Если строки не обновлены, вставьте строку.
  3. Если это не удается, вставьте новую строку.

Этот код выглядит так:

begin
    declare @maxid int;

    select @maxid = max(id)
    from inserted;


    update maxlastid
        set maxid = (case when lastid > @maxid then lastid else @maxid end)
        where year = year(getdate());

    if @@rowcount = 0
    begin try
        insert into maxlastid (year, lastid)
            values (year(getdate()), @maxid);
    end try;
    begin catch
        -- if the year already exists, try inserting again
        update maxlastid
            set maxid = (case when lastid > @maxid then lastid else @maxid end)
            where year = year(getdate());

        -- otherwise ignore
    end catch;
end;

Это может все еще иметь некоторую неуловимую гонку. Поэтому на самом деле рекомендуется использовать транзакции и блокировку.

person Gordon Linoff    schedule 05.12.2019
comment
Не могли бы вы проверить эту строку 'set maxid = (case when maxlastid ›maxid then maxlastid else maxid end)'? Разве maxlastid не должен быть lastid? - person Junaid; 05.12.2019
comment
Сэр, не могли бы вы ответить на этот вопрос: stackoverflow.com/questions/59193382/ - person Junaid; 06.12.2019

arrow_upward
1
arrow_downward

Я бы рекомендовал просто использовать представление:

CREATE VIEW dbo.vMaxLastId AS
  SELECT YEAR(CreatedAt) AS [Year], MAX(Id) AS [MaxId] 
    FROM Table1 
    GROUP BY YEAR(CreatedAt)

Если у вас много данных (миллионы записей) и они становятся медленными в использовании, вам нужно добавить индекс или сделать его кластеризованным представлением.

person tgralex    schedule 05.12.2019
comment
Спасибо за Ваш ответ. Вам не кажется, что ORDER BY должен быть GROUP BY? - person Junaid; 06.12.2019
comment
Да, я имел в виду GROUP BY, заказ по даже не разрешен в представлениях ... - person tgralex; 06.12.2019
comment
Сэр, не могли бы вы ответить на этот вопрос: stackoverflow.com/questions/59193382/ - person Junaid; 06.12.2019
comment
Проблема в том, что при удалении записи обновляется и представление. Например, если максимальная запись за год находится в представлении, а год изменяется, а затем максимальная запись за предыдущий год удаляется из родительской таблицы, представление будет обновлено. Но максимальная запись за предыдущий год должна храниться где-то в соответствии с моими требованиями. - person Junaid; 06.12.2019
comment
Он, безусловно, будет отражать только существующие данные, а не удаляться. - person tgralex; 06.12.2019
comment
В ЭТОМ ПРОБЛЕМА !! - person Junaid; 07.12.2019