DML Trigger ile Insert/Update/Delete script’ini loglamak

DDL/DML Trigger’lar auditing için çok sık kullanılan tetikleyicilerdir. Örneğin SQL Server’da Yazılım departmanına verdiğiniz username’i o departmandaki 10 kişi kullanıyor ve bir problem oldu bunu kimin yaptığını ispatlayamıyorsunuz. Bunun için DML Trigger yazarak Insert/Update veya Delete ‘i hangi PC’den, hangi IP’de ve hangi Saatte çalıştırıldığını loglayabilirsiniz.

Peki ya SQL cümleciği?
İşte bunu almak istediğinizde DDL ‘de oke ama DML trigger’da mağlesef mümkün olmuyor. Bunun için DBCC INPUTBUFFER ‘ı kullanıp o anki Session ID si ile calıştırılan komutu alabiliriz. Peki bunu nasıl trigger ile birleştirebilirim derseniz, örnek olarak hazırladığım Delete trigger’ını aşağıya ekliyorum.

Her nekadar istediğimizi bu şekilde çözsek de yüksek transaction’li DB’lerde kullanmanızı tavsiye etmem çünkü içeriğindeki Create/Drop/Insert’ler Database IO’nuzu yükseltecektir.

CREATE TABLE AuditYGT
(
 [ID] INT IDENTITY NOT NULL,
 [Action] NVARCHAR(20) NOT NULL,
 [Date] DATETIME DEFAULT(GETDATE()) NOT NULL,
 [IP] NVARCHAR(15) NOT NULL,
 [Logon] NVARCHAR(50) NOT NULL,
 [Hostname] NVARCHAR(50) NOT NULL,
 [SQL] NVARCHAR(MAX) NOT NULL
)
GO

CREATE TRIGGER trgAudit
ON dbo.Areas
 AFTER DELETE
AS
 SET NOCOUNT ON;
 CREATE TABLE #DBCC
 (
 EventType VARCHAR(50),
 Parameters VARCHAR(50),
 EventInfo NVARCHAR(MAX)
 )

 INSERT INTO #DBCC
 EXEC ('DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS')

 INSERT INTO AuditYGT
 (
 [Action],
 [IP],
 [Logon],
 [Hostname],
 [SQL]
 )
 SELECT 'Delete',
 (
 SELECT client_net_address
 FROM sys.dm_exec_connections
 WHERE Session_id = @@SPID
 ),
 SUSER_NAME(),
 HOST_NAME(),
 (
 SELECT EventInfo
 FROM #DBCC
 )
 FROM DELETED d;

 DROP TABLE #DBCC
GO