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

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

Я хотел бы иметь возможность поддерживать любой 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

Взгляните на команду CHECKSUM:

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

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

CHECKSUM

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

Ответ 2

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

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

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

Например решение с CHECKSUM_AGG(BINARY_CHECKSUM(*)) всегда будет 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!

Ответ 3

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

Ответ 4

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

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

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

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

Ответ 5

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

Ответ 6

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

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

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

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

Ответ 7

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

Ответ 8

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