Selçuk Yavuz

DDL Trigger'lar

0.Giriş

      SQL Server 2000'e kadar DML ( Data Manuplation Language ) Triggerlar yazabiliyorduk. Yani ancak bir tablo üzerinde gerçekleşen Insert, Update, Delete ifadeleri için kullanabiliyorduk. Ancak SQL Server 2005 ile gelen yeniliklerden bir taneside DDL ( Data Defination Language ) Trigger'lardır. Bunun anlamı, CREATE, ALTER ve DROP ifadeleri içinde Trigger'lar yazabileceğimizdir. Şimdi DDL Trigger'lar üzerinde detaylı inceleme yapacağız.

1. Basit bir güvenlik DDL Trigger'ı

      Hepimizin malumu ve web developer'ların korkulu rüyası SQL Injection. Sistemimize açık bulan aklı başında bir saldırganın ilk deneyeceği hacking yöntemlerinden biridir. Ekseriyetle bir tablo ismine ulaştıktan sonra deneyeceği komutlardan biride 'DROP TABLE TABLOISMI' şeklinde bir sorgu olacaktır. Aslında bunun en kolay önlemi uygulamalarını en düşük yetkideki kullanıcı ile çalıştırmaktır ancak geliştirilen uygulamaların genelinde güvenlik ve dökümantasyon önemli doneler sona bırakıldığı için sık sık göz ardı edilebilmektedir. Şu an biz bir veritabanı yöneticisi sorumluluğunda kendi önlemimizi alalım : Bunu için DROP TABLE ifadesinin işlemez hale getirelim.

CREATE TRIGGER ddl_tr_TabloSilinmez
ON DATABASE 
FOR DROP_TABLE
AS
	ROLLBACK
GO
 

     Yukarıdaki script ile oluşturduğumuz DDL Trigger Veritabanımız üzerinde DROP TABLE komutu çalıştırıldığında devreye girecektir. Triggerlar Implicit Transaction'ın bir parçasıdır. Tablo silindiğinde ROLLBACK diyerek Tablo Silme işlemi tamamlanmadan işlemi geri alabiliyoruz.

2. DDL Trigger'larına ayrıntılı bir bakış.

      Bir Trigger 4 parçadan oluşur. Tanımlama, Nerede , Ne zaman, Ne yapacak ?

2.1. Tanımlama

CREATE TRIGGER ddl_tr_TabloSilinemez 

Trigger’ın oluşturulması ve bir isim verilmesi.

2.2. Nerede

ON DATABASE 

Burada iki seçeneğimiz var. Oluşturacağımız Trigger üzerinde çalıştığımız veritabanı için oluşturulabilir veyahut tüm SQL Server için.

Eğer tüm SQL Server için çalışmasını istiyorsak ON ALL SERVER şekline tanımlamalıyız

2.3. Ne zaman

FOR DROP_TABLE 

ZAMAN İŞLEM_NESNE

Burayı ikiye ayırabiliriz :

İlk kelime. FOR, AFTER yada INSTEAD OF olabilir. AFTER yürütülen DDL komutu tamamen işletildikten sonra devreye girer. Örneğin ilişkili bir tablolardan biri siliniyorsa silme işleminde sorun yoksa tetiklenir. INSTEAD OF ise tüm işlemlerden önce tetiklenir.

İkinci kelime ise eylemi tanımlar. Örneğin DROP_TABLE tablonun silinmesini ifade eder. Burada Önce DDL komutu sonra obje ismi getirilebilir. Bir FUNCTION nesnesi oluşturulurken demek için CREATE_FUNCTION yeterlidir.

2.4. Ne Yapacak

AS            
   ROLLBACK  
GO  

 

Trigger’ımızn gövdesi ve ne yapmak istediğimizi buraya yazarız. Daha öncede belirttiğimiz gibi Trigger’lar Implicit Transaction’ın bir parçası oldukları için Implicit Transaction’a müdahele etmemiz mümkün. Yani DDL Trigger içerisinde yazacağımız ROLLBACK yada COMMIT ifadesi ile yürütülen DDL komutunu iptal edebilir yada onaylayabiliriz.

Açıkçası asli görevimiz burada başlıyor. “Ne yapacak” başlığını burada bitirip “Neler yapabiliriz” şeklinde daha geniş bir başlık açıp orada inceleyelim.

3. Neler yapabiliriz ?

      DML Trigger’larda sadece INSERT, UPDATE ve DELETE işlemleri ile çalışabildiğimizi belirtmiştir. Insert işleminde inserted, Delete işleminde deleted ve Update işleminde eski kayıtlar için deleted, yeni kayıtlar için için inserted tablolarının oluştuğunu ve işlemlerimiz bu sanal tablolarla hallettiğimizi hatırlayalım.

      Buna benzer olarak DDL Triggerlarda EVENTDATA() isimli fonksiyondan yürütülen DDL komutu ve durumu hakkında detaylı bilgi edinebiliriz. Bu fonksiyon bize XML formatında aşağıdaki bilgileri verir :

<EVENT_INSTANCE>
    <EventType>type</EventType>
    <PostTime>date-time</PostTime>
    <SPID>spid</SPID>
    <ServerName>name</ServerName>
    <LoginName>name</LoginName>
    <UserName>name</UserName>
    <DatabaseName>name</DatabaseName>
    <SchemaName>name</SchemaName>
    <ObjectName>name</ObjectName>
    <ObjectType>type</ObjectType>
    <TSQLCommand>command</TSQLCommand>
</EVENT_INSTANCE>
 

      SQL Server 2005 ile gelen yeniliklerden biri olan XML veri tipine bu sonucu atayım Xquery kullarak istediğimiz verilere ulaşabilir. Artık kim, ne zaman, ne yaptı inceleyebilir, ihtiyacımıza göre bu işlemleri bir tabloda loglayabilir, mail attırabilir yada iptal edebiliriz.

      Şimdi veritabanımızda yürütülen bazı DDL komutlarının logunu tutmak üzere bir tablo oluşturalım.

CREATE TABLE [dbo].[Log]
(
      [LogID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
      [PostTime] [datetime] NOT NULL,
      [UserName] [sysname] NOT NULL,
      [EventType] [sysname] NOT NULL,
      [TSQL] [nvarchar](max) NOT NULL
)
GO
 

      Kayıtları tutacağımız tablomuz hazır. Şimdi de veritabanımızda oluşturulan, değiştirilen ve silinen tabloların takibini yapabileceğimiz DDL Trigger’ımızı yazalım.

ALTER TRIGGER ddl_tr_Log
ON DATABASE 
FOR CREATE_TABLE,ALTER_TABLE,DROP_TABLE 
AS 
      DECLARE @eventdata XML
	  
      SET @eventdata = EVENTDATA()

      DECLARE @PostTime datetime
      DECLARE @UserName sysname
      DECLARE @EventType sysname
      DECLARE @TSQLCommand nvarchar(max)

      SET @PostTime = @eventdata.value('(/EVENT_INSTANCE/PostTime)[1]', 'sysname')
      SET @UserName = @eventdata.value('(/EVENT_INSTANCE/UserName)[1]', 'sysname')
      SET @EventType = @eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname')
      SET @TSQLCommand = @eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'sysname') 
 
      INSERT [dbo].[Log](PostTime,UserName,EventType,TSQLCommand) 
      VALUES (@PostTime,@UserName,@EventType,@TSQLCommand)
GO
 

4. Sonuç

      Oluşturduğumuz Trigger’ı Veritabanımızın altında Programmability -> Database Triggers altında görebilirsiniz

      Test için basit birer CREATE, ALTER ve DROP işlemlerinin sonucu :