Проверить изменения в таблице SQL Server?

Как я могу отслеживать изменения в таблице в базе данных SQL Server, не используя триггеры или каким-либо образом изменяя структуру базы данных? Моя предпочтительная среда программирования - .NET и C #.

Я хотел бы иметь возможность поддерживать любой SQL Server 2000 SP4 или новее. Мое приложение представляет собой надежную визуализацию данных для продукта другой компании. Наша клиентская база исчисляется тысячами, поэтому я не хочу предъявлять требования, чтобы мы изменяли таблицу сторонних поставщиков при каждой установке.

Под «изменениями в таблице» я подразумеваю изменения в данных таблицы, а не изменения в структуре таблицы.

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


Наилучшим способом действий с учетом моих требований (без триггеров или изменений схемы, SQL Server 2000 и 2005), по-видимому, является использование BINARY_CHECKSUMфункции в T-SQL . Способ, который я планирую реализовать, таков:

Каждые X секунд запускается следующий запрос:

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
FROM sample_table
WITH (NOLOCK);

И сравните это с сохраненным значением. Если значение изменилось, просмотрите таблицу строка за строкой, используя запрос:

SELECT row_id, BINARY_CHECKSUM(*)
FROM sample_table
WITH (NOLOCK);

И сравните возвращенные контрольные суммы с сохраненными значениями.

1.08.2008 12:35:56
Они случайно не ставили последнюю модифицированную метку времени в своих рядах, не так ли?
zmbq 3.11.2011 22:15:15
Для записи, если поддержка версии SQL Server 2005 или новее. Я посмотрю на компонент Service Broker в SQL Server.
Marco Guignard 7.08.2019 15:37:27
8 ОТВЕТОВ
РЕШЕНИЕ

Посмотрите на команду CHECKSUM:

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM sample_table WITH (NOLOCK);

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

СУММА

Вот как я использовал его для восстановления зависимостей кэша при изменении таблиц:
зависимость кэша базы данных ASP.NET 1.1 (без триггеров)

97
28.01.2013 03:44:42
Контрольные суммы могут и потерпят неудачу в конце концов. Если ваша система принимает, что два разных набора данных приведут к одной и той же контрольной сумме, то у вас все в порядке. По этой причине мне пришлось отойти от контрольных сумм в большинстве наших систем ...
LPains 26.05.2016 18:35:37
@ LPains, не могли бы вы уточнить ваше заявление?
petrosmm 25.07.2018 13:22:23
@petrosmm Я не уверен, что конкретно вы хотите, чтобы я уточнил, но я постараюсь. Представьте, что у вас есть таблица с несколькими сотнями записей, вы по сути генерируете целое число в виде контрольной суммы, как часто это будет конфликтовать? В моем случае я делал это с 10 таблицами, все с сотнями записей. У меня было по крайней мере одно столкновение в день. Проверьте этот другой ответ stackoverflow.com/questions/14450415/…
LPains 27.07.2018 15:00:19

Почему вы не хотите использовать триггеры? Это хорошая вещь, если вы используете их правильно. Если вы используете их как способ обеспечения ссылочной целостности, то есть когда они переходят от хорошего к плохому. Но если вы используете их для мониторинга, они не считаются табу.

25
26.01.2017 17:38:44

Иметь задание DTS (или задание, запущенное службой Windows), которое выполняется с заданным интервалом. Каждый раз, когда он запускается, он получает информацию о данной таблице с помощью системных таблиц INFORMATION_SCHEMA и записывает эти данные в хранилище данных. Сравните данные, возвращаемые относительно структуры таблицы, с данными, возвращенными в предыдущий раз. Если это отличается, то вы знаете, что структура изменилась.

Пример запроса для получения информации обо всех столбцах в таблице ABC (в идеале перечисляются только те столбцы из таблицы INFORMATION_SCHEMA, которые вам нужны, вместо использования * select **, как здесь):

select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'ABC'

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

16
1.08.2008 14:06:28
Речь идет об изменениях в данных таблицы, а information_schema содержит схему (определения столбцов) таблицы.
too 21.07.2014 08:15:31

Как часто вам нужно проверять изменения и насколько велики (с точки зрения размера строки) таблицы в базе данных? Если вы используете CHECKSUM_AGG(BINARY_CHECKSUM(*))метод, предложенный Джоном, он будет сканировать каждую строку указанной таблицы. NOLOCKПодсказка помогает, но на большой базе данных, вы все еще ударять каждую строку. Вам также нужно будет сохранить контрольную сумму для каждой строки, чтобы сообщить, что она изменилась.

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

Они могли бы реализовать API, обеспечивающий механизм уведомления вспомогательных приложений об изменении данных. Это может быть так же просто, как запись в таблицу уведомлений, в которой указано, какие таблицы и какие строки были изменены. Это может быть реализовано с помощью триггеров или кода приложения. С вашей стороны, это не имеет значения, вашей единственной заботой будет периодическое сканирование таблицы уведомлений. Производительность в базе данных будет намного меньше, чем сканирование каждой строки на наличие изменений.

Сложная задача - убедить поставщика приложений реализовать эту функцию. Поскольку это может быть полностью обработано через SQL с помощью триггеров, вы можете выполнить большую часть работы за них, написав и протестировав триггеры, а затем передав код поставщику приложений. Наличие у поставщика поддержки триггеров предотвращает ситуацию, когда добавление триггера непреднамеренно заменяет триггер, предоставленный поставщиком.

20
14.10.2012 12:19:25

Непростая догадка: если вы не хотите изменять сторонние таблицы, можете ли вы создать представление и затем включить триггер для этого представления?

13
5.08.2008 01:12:37

К сожалению, я не думаю, что есть чистый способ сделать это в SQL2000. Если вы сузите свои требования к SQL Server 2005 (и более поздним версиям), то вы в деле. Вы можете использовать SQLDependencyкласс в System.Data.SqlClient. См. Уведомления о запросах в SQL Server (ADO.NET) .

18
25.05.2012 20:42:28

К сожалению, CHECKSUM не всегда работает правильно, чтобы обнаружить изменения .

Это только примитивная контрольная сумма, а не циклическая проверка избыточности (CRC).

Поэтому вы не можете использовать его для обнаружения всех изменений, например, симметричные изменения приводят к одной и той же контрольной сумме!

Например решение с CHECKSUM_AGG(BINARY_CHECKSUM(*))всегда доставит 0 для всех 3 таблиц с различным содержанием:


SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM 
(
  SELECT 1 as numA, 1 as numB
  UNION ALL
  SELECT 1 as numA, 1 as numB
)  q
-- delivers 0!

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM ( SELECT 1 as numA, 2 as numB UNION ALL SELECT 1 as numA, 2 as numB ) q -- delivers 0!

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM ( SELECT 0 as numA, 0 as numB UNION ALL SELECT 0 as numA, 0 as numB ) q -- delivers 0!

29
14.05.2019 16:40:50
На самом деле это не ответ, а «ваше предложение не работает».
kristianp 18.01.2016 23:53:44
Это можно исправить для дублированных данных, используя ключевое слово DISINCT перед BINARY_CHECKSUM. Есть обсудили несколько других подводных камней здесь , но не совсем общие сценарии.
pblack 29.06.2016 16:22:42

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

6
24.07.2014 04:58:25
Пожалуйста, предоставьте документированный способ получения этой информации в виде таблицы в SQL Server
Martin Smith 6.10.2018 23:04:43