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.DescriptionsAFTER 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.DescriptionsAFTER 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.DescriptionsAFTER 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:
