Problem: Chcesz zalogować do tabeli polecenia jakimi użytkownicy modyfikują dane.
Komentarz: Możesz skorzystać z profilera, sesji eventów, ale jeśli chcesz to zrobić triggerem….
Rozwiązanie:
Załóżmy, że tabela, na której są wykonywane polecenia wygląda następująco:
CREATE TABLE TestTable
(ID INT IDENTITY PRIMARY KEY,
SomeData NVARCHAR(50))
GO
Stwórz tabelę do zapisywania komend użytkowników:
CREATE TABLE SQLCommands
(ID INT IDENTITY PRIMARY KEY,
SQLCmd NVARCHAR(4000))
GO
Pora na trigger:
CREATE TRIGGER tr_TestTable ON TestTable FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQLCmd NVARCHAR(4000)
DECLARE @TEMP TABLE
(EventType NVARCHAR(30), Parameters INT, EventInfo NVARCHAR(4000))
INSERT INTO @TEMP EXEC(’DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS’)
SELECT @SQLCmd = EventInfo FROM @TEMP
INSERT SQLCommands VALUES (@SQLCmd)
END
Co się tutaj dzieje:
- SET NOCOUNT ON powoduje, że podczas wykonywania triggera nic nie będzie dodatkowo wyświetlane na ekranie.
- DECLARE @SQLCmd… to zmienna, do której na chwilę trafi polecenie
- DECLARE @Temp … to deklaracja zmiennej tablicowej, która za chwilę będzie przechowywać wynik polecenia DBCC
- INSERT… wkłada do tabeli @Temp wynik polecenia DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS
- SELECT @SQLCmd = EventInfo FROM @TEMP – treść przechwyconego polecenia SQL jest wpisywane do zmiennej @SQLCmd
- I końcowy INSERT – zapisanie zmiennej @SQLCmd w stałej tabeli SQLCommands.
Pora przetestować rozwiązanie:
INSERT TestTable VALUES(’x’)
GO
UPDATE TestTable SET SomeData = 'y’
GO
DELETE FROM TestTable
GO
I sprawdzenie wyniku:
SELECT * FROM SQLCommands
