Ответ 1

Существует два распространенных способа создания контрольных журналов.

• Введите код доступа к данным.
• В самой базе данных с помощью триггеров.

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

Если вы делаете это в своем DA-слое, это в значительной степени зависит от вас. Вам просто нужно добавить код ко всем методам, которые сохраняются в базе данных, чтобы сохранить журнал изменений. Этот код аудита может быть в коде DA-кода или даже в ваших сохраненных procs в вашей базе данных, если вы используете хранимые procs для всего. По сути, это то же самое, когда вы вносите изменения в базу данных, регистрируйте это изменение.

Если вы хотите спуститься по маршруту триггеров, вы можете написать настраиваемые триггеры для каждой таблицы или создать более общий триггер, который будет работать одинаково на множестве таблиц. Проверьте эту статью о триггерах аудита. Это срабатывает при запуске триггеров всякий раз, когда происходит изменение, и триггеры регистрируют изменения. Помните, что если вы хотите проверять операторы SELECT, вы не можете использовать триггеры, вам придется сделать это с помощью кода/сохраненного аудита proc. Также стоит помнить, что в зависимости от вашей базы данных триггеры могут не срабатывать при любых обстоятельствах. Например, большинство баз данных не запускают триггеры во время инструкций TRUNCATE. Убедитесь, что ваши триггеры запущены в любом случае, если вам нужен аудит.

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

Что бы вы ни делали, вам нужно определить формат, который будет вести журнал аудита. Обычно вы сохраняете этот журнал в своей базе данных, но можете просто сохранить его в файле журнала или в соответствии с вашими требованиями. Вы можете использовать единую таблицу аудита, в которой регистрируются все изменения, или вы можете проверить аудиторскую таблицу на каждую основную таблицу. Для широкомасштабных реализаций вы даже можете рассмотреть возможность размещения таблиц аудита в полностью отдельной базе данных. Если вы регистрируетесь в таблице, обычно имеет поле "тип изменения", которое указывает, было ли проверенное изменение изменением стиля вставки, обновления или удаления вместе с измененными данными, пользователем, внесшим изменения, и датой/временем изменение было сделано. Не забудьте включить старые и новые данные для изменений стиля обновления.

Ответ 2

Наиболее универсальным методом было бы создание другой таблицы для хранения версий записи из первой таблицы. Затем вы можете удалить все данные из главной таблицы. Предположим, вам нужно управлять версиями таблицы Person (PersonId, Name, Surname):

CREATE TABLE Person 
(
   PersonId INT,                   // PK
   CurrentPersonVersion INT        // FK
);

CREATE TABLE PersonVersion
(
  PersonVersionId INT,             // PK
  PersonID                         // FK 
  Name VARCHAR,                    // actual data
  Surname VARCHAR,                 // actual data

  ChangeDate                       // logging data
  ChangeAuthor                     // logging data
)

Теперь для любых изменений требуется вставить новую PersonVersion и обновить CurrentPersonVersionID.

Ответ 3

Не используйте триггеры.

Любой, кто рассматривает возможность мягкого удаления, должен прочитать Ричард Динголл Проблема с мягким удалением.

Ответ 4

Лучший способ сделать это - настроить триггеры в базе данных, которые записывают в таблицы аудита.

Ответ 5

Другой способ сделать это отдельно от триггеров - это,

• У вас есть четыре столбца, UpdFlag, DelFlag, EffectiveDate и TerminatedDate для каждой таблицы, в которую вы хотите провести контрольный журнал.
• скопируйте свой sproc таким образом, что при выполнении обновления, чтобы передать все данные столбца строки в sproc, обновите строку, установив TerminatedDate на дату, которая была обновлена, и отметьте UpdFlag и вставить дату и время в столбец
• Затем создайте новую строку с новыми данными (которые действительно обновляются). и теперь установите новую дату для EffectiveDate и TerminatedDate, установленных на максимальную дату.

Аналогично, если вы хотите сделать удаление строки, просто обновите строку, пометив DelFlag как установлено, TerminatedDate с датой времени. Вы фактически выполняете мягкое удаление, а не фактическое sql Delete.

Таким образом, если вы хотите провести аудит данных и показать след изменений, вы можете просто фильтровать строки для тех, у которых есть набор UpdFlag, или между EffectiveDate и TerminatedDate. Аналогично для тех, которые были удалены, вы фильтруете те, у которых есть параметр DelFlag, или между EffectiveDate и TerminatedDate. Для текущих строк отфильтруйте строки, в которых установлены оба флага. Преимущество заключается в том, что вам не нужно создавать другую таблицу для аудита при использовании триггера!

Ответ 6

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

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

CREATE TRIGGER [dbo].[MyTable_CREATE_AUDIT]
ON [dbo].[MyTable]
AFTER UPDATE

AS

INSERT INTO MyTable_Audit 
(ItemID,LastModifiedBy,LastModifiedDate,field1,field2,field3,
field4,field5,AuditDate)
SELECT i.ItemID,i.LastModifiedBy,i.LastModifiedDate,

field1 = 
  CASE i.field1
    WHEN d.field1 THEN NULL
    ELSE i.field1
  END,

field2 = 
  CASE i.field2
    WHEN d.field2 THEN NULL
    ELSE i.field2
  END,

field3 = 
  CASE i.field3
    WHEN d.field3 THEN NULL
    ELSE i.field3
  END,

field4 = 
  CASE i.field4
    WHEN d.field4 THEN NULL
    ELSE i.field4
  END,  

field5 = 
  CASE i.field5
    WHEN d.field5 THEN NULL
    ELSE i.field5
  END,

GETDATE()

FROM inserted i
INNER JOIN deleted d
ON i.ItemID = d.ItemID

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

Это, безусловно, работает для меня. Может ли кто-нибудь увидеть какие-либо проблемы с этим подходом? Моя команда владеет как приложением, так и базой данных, поэтому возможные кривые шары, такие как изменения схемы, закрыты.

Ответ 7

Решение 1: сбор данных изменений SQL Server

https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-data-capture-sql-server?view=sql-server-2017

Сначала вам нужно включить сбор данных об изменениях в вашей базе данных.

USE AdventureWorks2012
GO  
EXEC sys.sp_cdc_enable_db  
GO  

Затем вы можете запросить изменения, используя fn_cdc_get_all_changes_ или fn_cdc_get_net_changes_.

-- ========  
-- Enumerate All Changes for Valid Range Template
-- ========  
USE AdventureWorks2012;  
GO  

DECLARE @from_lsn binary(10), @to_lsn binary(10);  
SET @from_lsn = sys.fn_cdc_get_min_lsn('HR_Department');  
SET @to_lsn   = sys.fn_cdc_get_max_lsn();  

SELECT * FROM cdc.fn_cdc_get_all_changes_HR_Department  
(@from_lsn, @to_lsn, N'all');  

Решение 2: Аудит базы данных SQL Server

Источник: https://www.dbaservices.com.au/how-to-configure-sql-server-auditing/

ENABLE DATABASE AUDITING

Аудит базы данных требует наличия аудита сервера (хотя и не обязательно спецификации аудита сервера). Однако аудит БД создается в пользовательской базе данных, которая должна проверяться, а не в базе данных master, где создается аудит сервера. Спецификации аудита базы данных можно найти в самой БД в разделе Безопасность → Спецификации аудита базы данных.

Чтобы создать аудит базы данных, вам нужно сначала USE базу данных (чтобы выбрать ее), а затем ниже приведен пример синтаксиса для аудита операций SELECT, UPDATE и DELETE для конкретных таблиц в этой базе данных;

USE UserDatabase
GO


CREATE DATABASE AUDIT SPECIFICATION [User_Database_Audit_Specification]  
FOR SERVER AUDIT [SQL_Server_Audit]
     ADD (SELECT , UPDATE , DELETE ON UserDatabase.dbo.Customer_DeliveryAddress BY dbo )
    ,ADD (SELECT , UPDATE , DELETE ON UserDatabase.dbo.DimCustomer_Email BY dbo )
    ,ADD (SELECT , UPDATE , DELETE ON UserDatabase.dbo.DimCustomer_Phone BY dbo )
WITH (STATE = ON) ;   
GO

Операции SELECT, UPDATE и DELETE - не единственные вещи, которые вы можете добавить в спецификацию аудита, хотя…

+------------+-------------------------------------------------------------------+
| Action     | Description                                                       |
+------------+-------------------------------------------------------------------+
| SELECT     | This event is raised whenever a SELECT is issued.                 |
| UPDATE     | This event is raised whenever an UPDATE is issued.                | 
| INSERT     | This event is raised whenever an INSERT is issued.                | 
| DELETE     | This event is raised whenever a DELETE is issued.                 | 
| EXECUTE    | This event is raised whenever an EXECUTE is issued.               | 
| RECEIVE    | This event is raised whenever a RECEIVE is issued.                | 
| REFERENCES | This event is raised whenever a REFERENCES permission is checked. | 
+------------+-------------------------------------------------------------------+

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

https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/security-audit-event-category-sql-server-profiler?view=sql-server-2017

Ответ 8

Я бы отправил триггерный маршрут, создав таблицу с аналогичной структурой до обновленной, с дополнительными столбцами для отслеживания изменений, таких как ModifiedAt и т.д. Затем добавим триггер обновления, который будет вносить изменения в эту таблицу. Мне легче поддерживать, чем все в коде приложения. Конечно, многие люди, как правило, забывают о триггерах, когда речь заходит о таких вопросах, как "wtf эта таблица меняется";) Приветствия.