Veritabanındaki Değişiklikleri DDL Trigger ile Yakalamak

DDL_TRIGGER_MSHOWTO

Bu yazımızda veri tabanı içerisinde Veri Tanımlama (DDL – Data Defition Language) işlemleri  gerçekleştirildiğinde ilgili sorguları takibini yapacak bir sistem kuracağız. Bu sayede veri tabanı tablo yapısında kim değişiklik yapmış, ne zaman yapmış gibi soruların cevabını önceden alıyor olacağız.

DDL Trigger’lar

Veri tanımlama işlemleri için oluşturulan bu Trigger türü veri tabanı veya sunucu kapsamında yazılırlar. Veritabanı kapsamında oluşturulan Trigger’lar oluşturuldukları veri tabanında nesneler olarak depolanır. Sunucu kapsamında oluşturulan veritabanları ise Master veritabanı içerisinde depolanırlar. Örneklendirecek olursak; Sql Server’a (Instance’a) login olarak bağlanan kişilerin kayıtlarını tutabiliriz, yada bizim gerçekleştireceğimiz örnek gibi veritabanında oluşturulan ve değiştirilen herhangi bir nesneyi yakalama işlemi içinde yazabiliriz.

Kullanacağımız örneğimizde DDL_DATABASE_LEVEL_EVENTS grubundan istisnasız tüm DDL eventlerini yakalayarak kaydedeceğiz.Yazacağımız Trigger içerisinde gerçekleştirilen işlem türü hakkında bilgi alabilmek için EVENTDATA() fonksiyonunu kullanacağız. Bu event Trigger’ın tetiklenmesi esnasında tetiklenen olay ile alakalı bir xml formatında çıktı üretir.

DDL Trigger Oluşturma İşlemi

Nesnelerin değişimini inceleyebilmek adına bir veritabanı ve tablo oluşturarak işlemleri adım adım örnekleyelim;

USE master;
GO
CREATE DATABASE MSHOWTO
ON
(   NAME = MSHOWTO,  
    FILENAME = 'C:\DATA\MSHOWTO.mdf',
    SIZE = 10,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 5 )  
LOG ON
(   NAME = MSHOWTO_Log,  
    FILENAME = 'C:\DATA\MSHOWTO_Log.ldf',
    SIZE = 5,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 5 );
GO

Sırada kayıtları tutacağımız tablomuzu oluşturmakta,

USE MSHOWTO
CREATE TABLE VT_KAYITLAR
(
     ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
     KAYIT_ZAMANI  DATETIME NOT NULL DEFAULT GETDATE(),
     EVENTCONTENT XML NULL
)
GO
CREATE NONCLUSTERED INDEX nci_Kayitlar ON dbo.Vt_Kayıtlar (KAYIT_ZAMANI ASC) INCLUDE (ID);
GO

DDL Trigger’ımızı veri tabanı seviyesinde tüm olayları kapsayacak şekilde yazıp oluşan işlemdeki bütün ayrıntıları kapsayacak xml içeriğimizi tablomuza kayıt edecek şekilde tanımlıyoruz.

 CREATE TRIGGER [TG_Vt_Kayitlar]
    ON DATABASE 
    FOR DDL_DATABASE_LEVEL_EVENTS 
    AS
    BEGIN
        SET NOCOUNT ON;
        IF OBJECT_ID('dbo.VT_KAYITLAR') IS NOT NULL
        BEGIN
            BEGIN TRY
                DECLARE @Eventcontent XML;
                SET @Eventcontent = EVENTDATA();
                INSERT dbo.VT_KAYITLAR (
                  KAYIT_ZAMANI
                , EVENTCONTENT
                )
                VALUES (
                  GETDATE()
                , @Eventcontent
                );
            END TRY
            BEGIN CATCH
                SET @Eventcontent= NULL;
            END CATCH
        END
    END
 GO

Trigger’ı yazarken kontrolleri sağlıklı yapmak önemlidir. Olası bir hata durumunda sistemin çalışmaz hale gelmesi istenilen bir durum değildir. Kod yazarken gerekli kontrolleri eklemeyi unutmayınız.

Resim-1

Kaydelilen Verileri Görüntüleme

Şimdi gerçekleştirilen işlemleri görüntüleyerek işlenilen verileri görelim.

USE [MSHOWTO]
GO
SELECT * FROM dbo.VT_KAYITLAR ORDER BY KAYIT_ZAMANI

Son olarak Content’imize eklenen Xml içeriği görelim,

<EVENT_INSTANCE>
  <EventType>CREATE_TRIGGER</EventType>
  <PostTime>2019-01-12T22:50:45.630</PostTime>
  <SPID>55</SPID>
  <ServerName>MOSTAR</ServerName>
  <LoginName>sa</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>MSHOWTO</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>TG_EMP</ObjectName>
  <ObjectType>TRIGGER</ObjectType>
  <TargetObjectName>emp</TargetObjectName>
  <TargetObjectType>TABLE</TargetObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText> 
-- =============================================
-- Author:	 Baki Abacı
-- =============================================
CREATE TRIGGER TG_EMP 
   ON  dbo.emp 
   AFTER   INSERT,DELETE,UPDATE
AS 
BEGIN
 
	SET NOCOUNT ON;
	PRINT('mshowto.org')

 

END
</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

Tetiklenme sonrası elde edilen bu xml’i ayrıştırarak veritabanına kayıt etme işlemi mümkündür. Makale sonunda yer alan kaynağa göz atarak özel ayrıştırma yapabilirsiniz.  Özetlersek,

Avantajlar,

  • Çok fazla ayrıntı içeriyor.
  • Tam olarak ne değiştini kesin görülebiliyor.
  • Yürütülen T-Sql deyimi anında alınabiliyor.
  • “Ne zaman?”  denildiğinde kesin sonuca varılıyor.
  • Kurulumu kolay.
  • Ek filtrelemeler yapılabilir.
  • Kolay görüntülenebiliyor.

 

Dezavantajlar,

  • Eski veriye erişim kalmıyor.
  • Verileri saklama seçeneği bulunmuyor.

Bu konuyla ilgili sorularınızı  alt kısımda bulunan yorumlar alanını kullanarak sorabilirsiniz.

Referanslar

www.mshowto.org

https://docs.microsoft.com/en-us/sql/relational-databases/triggers/ddl-event-groups?view=sql-server-2017 

TAGs: Veritabanı değişikliklerini yakalamak, DDL Trigger, SQL Server DDL, Sql Server Trigger , Master Veri tabanı, SQL Server

Yazı gezinmesi

Mobil sürümden çık