trigger oluşturup veritabanında olup biteni saklamak

Read Time:3 Minute, 16 Second

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

Ekleyen: Yiğit Aktan Microsoft – Data Platform Solution Architect Tarih:16.10.2011

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 mümkün ama DML trigger’da maalesef mümkün olmuyor. SQL Server 2008 ile gelen Change Data Capture (CDC) özelliğini kullanarak ilerlemek mümkün fakat SQL Server 2005 için konuşacak olursak DBCC INPUTBUFFER ‘ı kullanıp, o anki Session ID si ile calıştırılan komutu alabiliriz. CDC için ayrıca bir makale yazmayı planlıyorum. O yüzden çok fazla değinmeyeceğim. Peki DBCC INPUTBUFFER ‘ı kullanarak nasıl trigger ile birleştirebilirim derseniz, örnek olarak hazırladığım Delete trigger’ını aşağıya ekliyorum.

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

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.