Trigger logujący polecenie SQL, które wywołało triggera

9-Mar-2015

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

trigger

 

Źródło: http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/11/tsql2sday-using-sys-dm-exec-sql-text-to-get-the-calling-statement.aspx

Dodaj komentarz:

Autor: Rafał Kraik