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:
1 2 |
USE AdventureWorks2008; GO |
1 2 3 4 5 6 |
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 LogOperationsOnTablesON 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:
1 |
SELECT * FROM TableOperationsLog |
początkowo pokazuje, że tabela jest pusta. Jednak uruchomienie poleceń:
1 2 3 4 5 6 |
CREATE TABLE MyTest (Id INT); GO DROP TABLE MyTest; GO |
sprawia, że trigger uruchomił się 2 razy. Teraz wyświetlenie zawartości loga:
1 |
SELECT * FROM TableOperationsLog |
zwraca 2 rekordy:
Teraz już nic nie umknie Twojej uwadze!