Триггер SQL для таблицы аудита выходит из синхронизации

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

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

SELECT COUNT(*) FROM Movies WHERE DateAdded = Date.Now

Что ж, моя база данных содержит более 1 миллиона записей, и этот запрос выполняется примерно 1-2 тысячи раз в минуту, так что вы можете понять, почему я хотел использовать для этого новый подход.

Поэтому я создал таблицу аудита и настроил триггер SQL для обновления этой таблицы, когда в таблице Movie происходит какая-либо операция INSERT или UPDATE. Однако я заметил, что таблица аудита рассинхронизируется на несколько сотен каждый день (количество таблиц аудита выше, чем фактические обновления в таблице фильмов). Поскольку это не представляет большой проблемы, мне просто любопытно, что может быть причиной этого или как его отлаживать?

SQL-триггер:

ALTER TRIGGER [dbo].[trg_Audit]
ON [dbo].[Movies]
AFTER UPDATE, INSERT
AS
BEGIN
    UPDATE Audit SET [count] = [count] + 1 WHERE [date] = CONVERT (date, GETDATE())
    IF @@ROWCOUNT=0
    INSERT INTO audit ([date], [count]) VALUES (GETDATE(), 1)
END

Вышеупомянутый триггер происходит только после UPDATE или INSERT в таблице Movie и пытается обновить счетчик + 1 в таблице аудита, и если он не существует (IF @@ROWCOUNT=0), он затем создает его. Любая помощь приветствуется! Спасибо.


sql-server tsql audit-logging triggers counter
person bfritz    schedule 10.02.2014    source источник
comment
Вы понимаете, что если вы обновите 10 записей одновременно, этот триггер увеличит счетчик только на 1?   -  person OGHaza    schedule 10.02.2014
comment
@OGHaza, это нормально, поскольку я никогда не обновляю и не вставляю более одной записи за раз, а также количество таблиц аудита (проблема синхронизации) выше, чем то, что я возвращаю, когда запрашиваю свою таблицу фильмов с WHERE DateAdded = Date.Now. .. Вот это я и пытаюсь понять   -  person bfritz    schedule 10.02.2014
comment
Это означает, что мой триггер срабатывает больше, чем фактическое количество обновлений/вставок в базу данных. Я также проверил, чтобы убедиться, что при создании и #tempTables он не срабатывает, и это тоже не так.   -  person bfritz    schedule 10.02.2014
comment
Что ж, в таком случае что-то должно делать дополнительные обновления на вашем столе с фильмами.   -  person OGHaza    schedule 10.02.2014
comment
При использовании ПОСЛЕ ОБНОВЛЕНИЯ, ВСТАВИТЬ это означает, что он будет запускаться только после успешного обновления/вставки правильно?   -  person bfritz    schedule 10.02.2014
comment
Да, вы меняете DateAdded при обновлении? Потому что, очевидно, иначе WHERE DateAdded = Date.Now вернул бы только счетчик INSERT.   -  person OGHaza    schedule 10.02.2014
comment
Казалось бы, индексированное представление, построенное на основе таблицы Movies, было бы более разумным — SQL Server позаботился бы об его обслуживании автоматически, и вы могли бы покончить со своими триггерами.   -  person Damien_The_Unbeliever    schedule 10.02.2014
comment
@Damien_The_Unbeliever звучит интересно. Я никогда раньше не устанавливал представление в SQL, сейчас я использую SQL 2008, и до того, как я пошел по маршруту триггера, я нашел несколько способов справиться с этим с помощью РЕАЛЬНОЙ функции аудита, однако это не похоже для установки на моем сервере.   -  person bfritz    schedule 10.02.2014

Ответы (2)


arrow_upward
2
arrow_downward

Что-то вроде этого должно работать:

create table dbo.Movies (
    A int not null,
    B int not null,
    DateAdded datetime not null
)
go
create view dbo.audit
with schemabinding
as
    select CONVERT(date,DateAdded) as dt,COUNT_BIG(*) as cnt
    from dbo.Movies
    group by CONVERT(date,DateAdded)
go
create unique clustered index IX_MovieCounts on dbo.audit (dt)

Это называется индексированным представлением. Преимущество в том, что SQL Server берет на себя ответственность за сохранение данных, хранящихся в этом представлении, и это всегда правильно.

Если вы не используете версию Enterprise/Developer, вы должны запросить представление audit, используя подсказку NOEXPAND:

SELECT * from audit with (noexpand)

Это имеет преимущества, которые

а) Теперь вам не нужно самостоятельно писать триггеры (на самом деле в SQL Server есть что-то очень похожее на триггеры за кулисами),

б) Теперь он может справляться с многострочными вставками, обновлениями и удалениями, и

c) Вам не нужно писать логику, чтобы справиться с обновлением, которое изменяет значение DateAdded.

person Damien_The_Unbeliever    schedule 10.02.2014
comment
Привет, Дэмиен, я только что протестировал это в своей среде разработки, и, похоже, оно работает и обновляется должным образом, однако я хотел знать, будет ли это иметь больше накладных расходов, чем функция триггера? Как видите, мне пришлось прибегнуть к некоторым трюкам внутри триггера, чтобы избежать оператора select для проверки существования строки перед попыткой обновления. Поскольку я получаю более 15 миллионов запросов в день, мне нужно убедиться, что это не приведет к резкому увеличению нагрузки на мой сервер. Когда эти представления обновляются? на лету? или как триггер после каждой вставки/обновления? - person bfritz; 10.02.2014
comment
@bfritz - они почти такие же, как если бы вы написали триггер, а правила, касающиеся того, что разрешено в индексированном представлении, предназначены для обеспечения их эффективности, т. Е. Они явно разработаны так, чтобы поддержка индекса могла быть достигнуто на основе исключительно строк, на которые влияет конкретный оператор - им никогда не нужно сканировать всю таблицу. - person Damien_The_Unbeliever; 10.02.2014
comment
Просто хотел добавить, что я внедрил это в производство и обнаружил, что без использования WITH (NOEXPAND) запросы выполняются очень медленно! Так что еще раз спасибо за это! - person bfritz; 10.02.2014
comment
@bfritz - да, без подсказки noexpand он в основном игнорирует всю проделанную вами работу и запускает запрос по всей базовой таблице, игнорируя индекс. Это один из способов, которым MS пытается продать вам Enterprise Edition. - person Damien_The_Unbeliever; 10.02.2014

arrow_upward
1
arrow_downward

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

UPDATE Audit 
SET [count] = [count] + (SELECT COUNT(*) FROM INSERTED)
WHERE [date] = CONVERT (date, GETDATE())
IF @@ROWCOUNT=0
INSERT INTO audit ([date], [count]) 
VALUES (GETDATE(), (SELECT COUNT(*) FROM INSERTED))
person OGHaza    schedule 10.02.2014
comment
Я не вижу ничего плохого, поэтому, пока вы не попробуете, я могу только предполагать, что это так. Этот триггер отлично работает на SQLFiddle. - person OGHaza; 10.02.2014