Audyt operacji na tablach z wykorzystaniem DDL Trigger

SQL Server umożliwia tworzenie triggerów nie tylko na operacje DML (wstawianie, modyfikacja i usuwanie rekordów), ale także na poziomie bazy danych (tworzenie, modyfikacja i usuwanie obiektów bazodanowych np tabel, widoków, procedur itp) a nawet na poziomie serwera (tworzenie obiektów serwera, czy  śledzenie logowania się uzytkowników).

O ile zwykłe triggery nakłada się na tabele FOR UPDATE, INSERT, DELETE o tyle triggery na poziomie bazy danych nakłada się na takie zdarzenia jak np. CREATE_TABLE, ALTER_TABLE czy DROP_TABLE. Dzięki temu możesz zbudować własny mechanizm audytu takich zdarzeń: 

Poniższy przykład pokazuje, jak zapisywać informacje o tym kto i kiedy założył, zmodyfikował lub usunął tabelę.

Zaczniemy od utworzenia specjalnej tabeli zbierającej tego typu informacje:

USE AdventureWorks2008;
GO

CREATE TABLE TableOperationsLog
( Id INT IDENTITY,
  Command VARCHAR(MAX),
  UserName VARCHAR(100),
  OperationDate DATETIME);
GO

Teraz najważniejsze. Tworzymy trigger DDL dla typowych czynności związanych z  tabelą, czyli CREATE, ALTER i DROP:

CREATE TRIGGER LogOperationsOnTables
ON DATABASE FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLEAS 
DECLARE @Command VARCHAR(MAX);  
SET @Command = EVENTDATA().value(‚(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’nvarchar(max)’)  
INSERT INTO TableOperationsLog  VALUES     (@Command, USER_NAME(), GETDATE());

GO

Na uwagę zasługuje tu szczególnie:

• ON DATABASE – trigger będzie pracował na obiektach bazy danych (trigger dotyczy operacji DDL, a nie DML)
• FOR CREATE_TABLE … – triger uruchomii się kiedy tylko zostanie wykonana czynność związana z tworzeniem, zmianą lub usunięciem tabeli
• SET @Command = EVENTDATA().value(‚(/EVENT_INSTANCE …- każdy trigger DDL otrzymuje ‚do dyspozycji” dane zapisane w postaci XML. Dostęp do tych danych można uzyskać za pomocą funkcji EVENTDATA(). Odwłując się do wartości klucza /EVENT_INSTANCE/TSQLCommand/CommandText otrzymujesz listę komend SQL użytych w trakcie obsługiwanego zdarzenia. My z tej listy pobieramy pierwszą (i jedyną) instrukcję: (/EVENT_INSTANCE/TSQLCommand/CommandText)[1]

Pora zatem zobaczyć, jak to działa:

SELECT * FROM TableOperationsLog

początkowo pokazuje, że tabela jest pusta. Jednak uruchomienie poleceń:

CREATE TABLE MyTest
(Id INT);
GO

DROP TABLE MyTest;
GO

 sprawia, że trigger uruchomił się 2 razy. Teraz wyświetlenie zawartości loga:

SELECT * FROM TableOperationsLog

zwraca 2 rekordy:

Teraz już nic nie umknie Twojej uwadze!