Как отслеживать изменения данных в таблице базы данных
Каков наилучший способ отслеживать изменения в таблице базы данных?
Представьте, что у вас есть приложение, в котором пользователи (в контексте приложения, а не пользователи БД) могут изменять данные, которые хранятся в некоторой таблице базы данных. Каков наилучший способ отслеживать историю всех изменений, чтобы вы могли показать, какому пользователю в какое время изменить какие данные и как?
8 ответов
В целом, если ваше приложение структурировано по слоям, вызовите на уровне доступа к данным хранимую процедуру на сервере базы данных, чтобы записать журнал изменений базы данных.
В языках, которые поддерживают такую вещь, аспектно-ориентированное программирование может быть хорошим методом для такого рода приложений. Аудит изменений в таблице базы данных - это вид операции, который обычно требуется регистрировать для всех операций, поэтому AOP может работать очень хорошо.
Помните, что регистрация изменений в базе данных создаст много данных и замедлит работу системы. Может быть целесообразно использовать решение для очереди сообщений и отдельную базу данных для ведения журнала аудита, в зависимости от размера приложения.
Также вполне возможно использовать хранимые процедуры для обработки этого, хотя может потребоваться некоторая работа, связанная с передачей учетных данных пользователя в саму базу данных.
У вас есть несколько вопросов, которые не очень хорошо связаны друг с другом.
На базовом уровне базы данных вы можете отслеживать изменения, имея отдельную таблицу, в которую добавляется запись с помощью триггеров в операторах INSERT/UPDATE/DELETE. Это общий способ отслеживания изменений в таблице базы данных.
Другая вещь, которую вы хотите, это знать, какой пользователь внес изменения. Обычно ваши триггеры не знают об этом. Я предполагаю, что если вы хотите знать, какой пользователь изменил часть данных, то возможно, что несколько пользователей могут изменить одни и те же данные.
Нет правильного способа сделать это, возможно, вы захотите иметь отдельную таблицу, в которую код вашего приложения будет вставлять запись, когда пользователь обновляет некоторые данные в другой таблице, включая пользователя, метку времени и идентификатор измененной записи.
Убедитесь, что вы используете транзакцию, чтобы избежать случаев, когда обновление выполняется без вставки, или если вы выполняете обратный порядок, вы не заканчиваете вставку без обновления.
Один метод, который я видел довольно часто, - это иметь таблицы аудита. Затем вы можете показать только то, что изменилось, что изменилось и из чего оно изменилось, или все, что душе угодно:) Затем вы могли бы написать триггер для фактической регистрации. Не слишком больно, если все сделано правильно...
Независимо от того, как вы это делаете, это зависит от того, как ваши пользователи подключаются к базе данных. Используют ли они одного пользователя приложения через контекст безопасности в приложении, подключаются ли они, используя свои собственные учетные записи в домене, или в приложении только все подключаются с общей учетной записью sql?
Если вы не можете получить информацию о пользователе из соединения с базой данных, это немного больше боли. И затем вы можете посмотреть на ведение журналов в приложении, поэтому, если у вас есть процесс с именем "CreateOrder" или что-то еще, вы можете войти в таблицу Order_Audit или что-то еще.
Выполнение всего этого в приложении открывает немного больше возможностей для внесения изменений, внесенных извне приложения, но если у вас есть несколько приложений, использующих одни и те же данные, и вы просто хотели увидеть, какие изменения были внесены вашим, возможно, это то, что вы хотели... < пожимает плечами>
Удачи тебе, хотя!
--Kevin
Исследуя этот же вопрос, я нашел обсуждение здесь очень полезным. Он предлагает установить параллельную таблицу для отслеживания изменений, где каждая таблица отслеживания изменений имеет те же столбцы, что и отслеживаемые, плюс столбцы для того, кто ее изменил, когда и если он был удален. (Должна быть возможность генерировать схему для этого более или менее автоматически с использованием пересмотренной версии ваших ранее существующих сценариев.)
Предположим, у меня есть таблица Person с 10 столбцами, которые включают PersonSid и UpdateDate. Теперь я хочу отслеживать любые обновления в Персональной таблице. Вот простая техника, которую я использовал:
Создать таблицу person_log
создать таблицу person_log(дата datetime2, sid int);
Создайте триггер для таблицы Person, который будет вставлять строку в таблицу person_log при каждом обновлении таблицы Person:
создать триггер tr на dbo.Person
для обновления
как вставить в person_log(дата, sid) выберите обновленный DTM, PersonSID из вставленного
После каких-либо обновлений запросите таблицу person_log, и вы увидите обновленный personSid. То же самое вы можете сделать для вставки, удаления.
Выше приведен пример для SQL, дайте мне знать в случае каких-либо запросов или используйте эту ссылку: http://www.4guysfromrolla.com/webtech/042507-1.shtml
Давайте попробуем с этим компонентом с открытым исходным кодом:
https://tabledependency.codeplex.com/
TableDependency - это общий компонент C#, используемый для получения уведомлений при изменении содержимого указанной таблицы базы данных.
Журнал трассировки в отдельной таблице (с идентификатором столбца, возможно, с отметками времени)?
Собираетесь ли вы также отменить изменения - возможно, предварительно создать оператор отмены (DELETE для каждой INSERT, (не) UPDATE для каждого обычного UPDATE) и сохранить его в трассировке?
Если все изменится с php. Вы можете использовать класс для регистрации всех INSERT/UPDATE/DELETE перед запросом. Это будет действие сохранения, таблица, столбец, newValue, oldValue, дата, система(при необходимости), ip, UserAgent, clumnReference, operatorReference, valueReference. Все таблицы / столбцы / действия, которые должны регистрироваться, являются настраиваемыми.