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
TAGs: Veritabanı değişikliklerini yakalamak, DDL Trigger, SQL Server DDL, Sql Server Trigger , Master Veri tabanı, SQL Server