Audyt w oparciu o trigger

19-Wrz-2013

Budując audyt możesz skusić się na śledzenie modyfikacji rekordów w taki sposób aby zapamiętać zmianę każdego rekordu, jego wstawienie lub usunięcie tak, aby wiadomo było kto i kiedy był autorem jakiej poprawki. Łatwo to osiągnąć stosując triggery, ale pamiętaj, że nie jest to w 100% poprawny audyt, bo triggery administrator może wyłączyć, albo może skasować dane zalogowane w tabelce śledzącej zmiany.

Załóżmy że masz tabelę:

CREATE TABLE dbo.Descriptions
(DescriptionId INT IDENTITY PRIMARY KEY,
DescriptionText NVARCHAR(1000))

Chcesz śledzić każde wstawienie, modyfikację i usunięcie rekordu, więc możesz zdefiniować tabelkę log monitorującą te zmiany:

CREATE TABLE dbo.DescriptionsLog
(LogId INT IDENTITY PRIMARY KEY,
DescriptionId INT,
DescriptionText NVARCHAR(1000),
Action CHAR(1),
UserName SYSNAME,
ActionTime DateTime2)

Do tabelki dodano:

  • LogId – numer wpisu z loga
  • DescriptionId i DescriptionText, czyli wszystkie kolumny z tabelki, którą chcesz śledzić
  • Action – pola na literę I dla Insertu, U dla Update i D dla Delete
  • UserName – aby było wiadomo kto wprowadził zmianę
  • ActionTime, aby było wiadomo kiedy wprowadzono zmianę

Oto, jak możesz obsłużyć wstawienie rekordu:

CREATE TRIGGER dbo.tr_descriptions_insert ON dbo.Descriptions
AFTER INSERT
AS
SET NOCOUNT ON
INSERT DescriptionsLog
SELECT *,’I”,SUSER_SNAME(),SYSDATETIME() FROM inserted

Po wstawieniu rekordu uruchomi się trigger, który pobierze informację o wszystkich wstawionych rekordach z tabeli inserted i uzupełni je o literkę I, nazwę bieżącego użytkownika oraz bieżący czas. Dane te zapisze do tabeli log-a.

Oto, jak możesz obsłużyć aktualizację:

CREATE TRIGGER dbo.tr_descriptions_update ON dbo.Descriptions
AFTER UPDATE
AS
SET NOCOUNT ON
INSERT DescriptionsLog
SELECT *,’U’,SUSER_SNAME(),SYSDATETIME() FROM inserted

… czyli postępujemy podobnie jak przy insert, tylko literka I zmieniła się na U. Podczas aktualizacji masz do dyspozycji 2 tabele wirtualne. Inserted (z nową zawartością rekordu) oraz deleted (ze starą zawartością rekordu). Nas interesowały nowe wartości, więc skorzystaliśmy z tabelki inserted.

Oto, jak możesz obsłużyć usuwanie rekordu:

CREATE TRIGGER dbo.tr_descriptions_delete ON dbo.Descriptions
AFTER DELETE
AS
SET NOCOUNT ON
INSERT DescriptionsLog
SELECT *,’D’,SUSER_SNAME(),SYSDATETIME() FROM deleted

Tym razem przepiszesz literkę D, a dane pobierzesz z widocznej w triggerze delete wirtualnej tabelki deleted, prezentującej wartości aktualnie kasowanych rekordów.

Oto wyniki. Wykonaj takie polecenia:

INSERT dbo.Descriptions VALUES (‚Opis dla rekordu nr 1. wersja 1’)
INSERT dbo.Descriptions VALUES (‚Opis dla rekordu nr 2. Wersja 1’)
UPDATE dbo.Descriptions SET DescriptionText=’Opis dla rekordu nr 1. wersja 2′ WHERE Descriptionld=1
UPDATE dbo.Descriptions SET DescriptionText=’Opis dla rekordu nr 1. wersja 3′ WHERE Descriptionld=1
UPDATE dbo.Descriptions SET DescriptionText=’Opis dla rekordu nr 2. wersja 2′ WHERE Descriptionld=2
DELETE dbo.Descriptions WHERE Descriptionld=2

Każde z tych poleceń nie tylko zmieniało wartości w tabeli Descriptions, ale poprzez triggery przenosiło modyfikację także do tabelki Description_log:

history audit table

Dodaj komentarz:

Autor: Rafał Kraik