Makale Özeti

Triggerların sadece manüplasyon işlemleri üzerinde olması, Data Definition Language (DDL) üzerinde tanımlanamamaları kullanım alanlarını kısıtlamaktaydı. SQL Server 2005 ile gelen yeniliklerden biri de DDL Triggerlarıdır. DDL Triggerları nasıl yazabileceğimizi inceleyelim.

Makale

SQL Server 2005'te DDL Triggerları

    SQL server 2000 de dahil olmak üzere, bugüne kadar ki tüm SQL Server versiyonları oldukça sınırlı trigger desteğine sahipler. Her ne kadar, triggerların yapısı ve performansı bakımından çoğu zaman kullanımını iki kez düşünmek zorunda kalsak ta fazla esnek olmayan, sadece manuplasyon(DML) işlemlerini (INSERT / UPDATE / DELETE) izleyebilen triggerlarla çok işler başarabilirdik  / başardık.

    Fakat triggerların sadece manüplasyon işlemleri üzerinde olması, Data Definition Language (DDL) üzerinde tanımlanamamaları kullanım alanlarını kısıtlamaktaydı. SQL Server 2005 ile gelen yeniliklerden biri de DDL Triggerlarıdır. Diğer bir deyişle, database üzerinde tablo yaratılırken, kullanıcı silinirken, bir fonksiyon tanımlanırken ve ya kabaca CREATE, ALTER, DROP işlemlerini de izleyebileceğimiz triggerlar kullanımımıza sunulmuştur. Peki, nasıl yazacağız bu triggerları ve ne işimize yarayacak?

    Hemen bir örnek verelim. database üzerinde yaratılan tüm tabloların bir listesini istesem? (soruya dikkat edelim. Şu anda bulunan tabloların listesini istiyor olsaydım, bunu alıştığımız sistemle sysobject tablosunu sorgulayarak yapabilirdim. Ben database ilk yaratıldığı andan itibaren silinen, silinmeyen tüm tablolari istiyorum) Öyleyse, bu tabloların isimlerini, yapısının nasıl olduğunu tutan bir tabloya ihtiyacım var. Tek kolonlu "YaratilanTablolar" tablosu yaratalım. Kolonun data tipi xml olsun.

ve triggerımızı yazalım :

 
CREATE trigger trgTableYarat
on Database
for CREATE_TABLE
as
          DECLARE @rEventData XML
          SET @rEventData = eventdata() 
insert YaratilanTablolar
select @rEventData as xmlData
 
GO
ENABLE TRIGGER [trgTableYarat] ON DATABASE  

Trigger da dikkat çekmesi gereken keywordler :

on Database : Önceden, DML triggerlarının hangi tablo üzerinde çalışacağını belirlemek için on Tablo_adı yazardık. Şimdi ise, bu bir DDL triggerı olduğu ve database'i dinleyeceği için on Database diyoruz.

for CREATE_TABLE: Hangi eventi yakalayacağımızı belirliyoruz. Sorum sadece yeni yaratılan tablolardı. Öyleyse CREATE TABLE statementı çalışacak ve bu eventi tetikleyecektir. eventlerin detaylı bir listesini makalenin sonunda bulabilirsiniz.

eventdata()  : Yakaladığımız eventin hangi dataları içerdiğini alacağımız, xml döndüren fonksiyonumuz.

Bu triggerı sorunsuz bir şekilde derledikten sonra, herhangi bir ve ya bir kaç tablo yaratın. Ben de rastgele bir tablo yarattım ve "YaratilanTablolar" tablosunda oluşturduğu kayıt aşağıdaki şekilde oluştu. İnceleyelim:

<EVENT_INSTANCE>
     <EventType>CREATE_TABLE</EventType>
     <PostTime>2005-10-27T19:02:48.250</PostTime>
     <SPID>52</SPID>
     <ServerName>SUPRADYN\YKN1</ServerName>
     <LoginName>BILGEADAM\kivanc.ozuolmez</LoginName>
     <UserName>dbo</UserName>
     <DatabaseName>testDB</DatabaseName>
     <SchemaName>dbo</SchemaName>
     <ObjectName>testTablosu1</ObjectName>
     <ObjectType>TABLE</ObjectType>
     <TSQLCommand><SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
          <CommandText>CREATE TABLE dbo.testTablosu1
                 (
                 ID int NOT NULL,
                 Tanim int NOT NULL
                 )  ON [PRIMARY]
          </CommandText>
     </TSQLCommand>
</EVENT_INSTANCE> 

    Gördüğümüz XML datası trigger tetiklendiği andaki bir çok datası toplanarak oluşturulmuş o anki calışma değerleridir. Gördüğünüz gibi, çalıştırılan T-SQL Scriptten, çalışma zamanına, kullanıcı adına, çalışma zamanına dek bir çok dataya buradan erişilebilir. Bu "YaratilanTablolar" tablosunda yalnizca bir tabloya ait kayıtlar. Başka tablolar eklendikce, aynı şemada XML dataları YaratilanTablolar'a eklenecektir.

Peki, biz trigger sadece tablo ismini ve ya zamanı ve ya bu XML datadan istediğimiz bölümü kaydetsin istiyorsak? Bu da basit bir xquery ile çözümlenebilir.

CREATE trigger trgTableYarat2
on Database
for CREATE_TABLE
as
          DECLARE @rEventData XML
 
          SET @rEventData = eventdata()
insert YaratilanTablolarDetay
 
select @rEventData.query ('data(/EVENT_INSTANCE/PostTime)') AS [Calisma Zamani],
         @rEventData.query ('data(/EVENT_INSTANCE/EventType)') AS Tip,
         @rEventData.query ('data(/EVENT_INSTANCE/ServerName)') AS Server,
         @rEventData.query ('data(/EVENT_INSTANCE/TSQLCommand/CommandText)') AS Script
 
GO
ENABLE TRIGGER trgTableYarat2 ON DATABASE
 

    ("YaratilanTablolarDetay" tablosunun kolon yapisi, ne şekilde olmasi gerektiği zaten anlaşılacağı için ayrıca bahsetmeyeceğim.) Gördüğünüz gibi, yaptığımız işlem yalnızca @rEventData xml verisini sorgulamak oldu. Benzer örnekler çoğaltılabilinir. Öğrenmemiz gereken, hangi eventlerin olduğu ve gelen XML datasının içerisinde hangi veriye nasıl ulaşacağımızdır. Bunun dışında kalan herşey yukarıda gördüğümüz örneklere benzer şekilde T-SQL kullanarak çözümlenebilir.

DDL Triggerları, diğer triggerlar gibi birer programming object'tir. Fakat database seviyesinde bulunduğu için, Management Studio'da databasename / programmability /database triggers altından görünebilir. İsterseniz, aşağıdaki gibi sorgulayarak ta bu triggerlarla ilgili bilgilere ulaşabilirsiniz.

 

select * from sys.triggers


Sistemte var olan bu triggerların hangi olayları dinlediğini görmek için ise şu query kullanılabilir:

 

Select * from sys.trigger_events

SQL Server 2005 in en güzel özelliklerinden biri olan, CLR entegrasyonu, programming objelerinin .NET ortamında yazılması / çalıştırılmasıdır. DDL Triggerlar'da yukarıda yaptığımız örneklerin yanı sıra, VB.NET ve ya C# ta da yazılabilir. Hem DDL triggerları, hem de DML triggerlarını CLR da nasıl yazabileceğimize buradan ve buradan inceleyebilirsiniz.

DDL eventlerinin listesi şu şekildedir. Bu liste MS SQL Server 2005 Beta 2 içindir ve SQL Server tam ürün olarak piyasaya çıktığında değişiklikler içerebilir.

Create_Table

Drop_Assembly

Create_Service

Alter_Table

Create_Type

Alter_Service

Drop_Table

Drop_Type

Drop_Service

Create_View

Create_User

Create_Route

Alter_View

Alter_User

Alter_Route

Drop_View

Drop_User

Drop_Route

Create_Synonym

Create_Role

Create_Binding

Drop_Synonym

Alter_Role

Alter_Binding

Create_Function

Drop_Role

Drop_Binding

Alter_Function

Create_AppRole

Grant_Server

Drop_Function

Alter_AppRole

Deny_Server

Create_Procedure

Drop_AppRole

Revoke_Server

Alter_Procedure

Create_Schema

Grant_Database

Drop_Procedure

Alter_Schema

Deny_Database

Create_Trigger

Drop_Schema

Revoke_Database

Alter_Trigger

Create_Login

Create_Secexpr

Drop_Trigger

Alter_Login

Drop_Secexpr

Create_Event_Notification

Drop_Login

Create_XmlSchema

Drop_Event_Notification

Create_Endpoint

Alter_XmlSchema

Create_Index

Alter_Endpoint

Drop_XmlSchema

Alter_Index

Drop_Endpoint

Create_Cert

Drop_Index

Create_MsgType

Alter_Cert

Create_Stats

Alter_MsgType

Drop_Cert

Update_Stats

Drop_MsgType

Create_Partition_Function

Drop_Stats

Create_Contract

Alter_Partition_Function

Create_Database

Alter_Contract

Drop_Partition_Function

Alter_Database

Drop_Contract

Create_Partition_Scheme

Drop_Database

Create_Queue

Alter_Partition_Scheme

Create_Assembly

Alter_Queue

Drop_Partition_Scheme

Alter_Assembly

Drop_Queue

 

 

 

İyi çalışmalar

Kivanc OZUOLMEZ