Makale Özeti

SQL Server 2005 ile duyurulan CLR desteğini Stored Procedureler ile kullanmayı inceledik bu makale seriisinde ise CLR destekli Trigger yazmayı inceleyeceğiz.

Makale

CLR Destekli Trigger Yazmak - I

 

Merhabalar, önceki iki makalede Stored Procedure yazmaktan bahsetmiştik, şimdi ise SQL Server a CLR destekli Trigger yazmaktan bahsedeceğiz. Aslında temel olarak yapacağımız işlem, Stored Procedure yazmak gibi, ancak triggerin kendi yapısal olarak bazı değişiklikleri var, makalede bu değişiklikleri tanıyıp, nasıl bir Vb.Net ile nasıl bir trigger yazarız, nelere dikkat etmeliyizi inceleyeceğiz, bunun yanında özellikle değinmek istediğim bir başka konu ise Visual Studio 2005 ile deploy etmeden kendi assembly inizi nasıl SQL Server 2005 e ekleyeceğiniz ve bu Assebmly içinden nasıl Stored Procedure ve Trigger tanımlayacağınız olacak.

İlk önce Visual Studio 2005 inizi açın Create Project deyin, Visual Basic in altından Database i seçip sol taraftanda SQL Server Project i seçin, isim olarak ise YazGelistirOrnek yazın (Burada size hangi veri kaynağını kullanacağınızı soracak, AdventureWorks ü seçebilirsiniz, biraz ileride nasıl değiştirebileceğinizide anlatacağım.).

Şimdi işlemlerimizi yapmak için bir veritabanı ve bir tablo oluşturalım, SQL Server Management Studio yu açın ve Databases klasörüne sağ tıklayıp New Database e tıklayın, Database name bölümüne ise yine YazGelistirOrnek yazalım ve OK e basalım. Şimdi bu database e yeni bir tablo oluşturalım, ismi Users olsun, Script i aşağıdaki gibi olsun.

CREATE TABLE [dbo].[Users](

      [UserId] [tinyint] IDENTITY(1,1) NOT NULL,

      [UserName] [nvarchar](20) COLLATE Latin1_General_CI_AI NULL,

      [UserAge] [tinyint] NULL,

 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED

(

      [UserId] ASC

) ON [PRIMARY]

) ON [PRIMARY]

Gördüğünüz gibi 3 sütunlu bir Tablo oluşturduk. Şimdi bu Database ve bu tablo için bazı Trigger denemeleri yapacağız. Ancak ilk önce oluşturduğumuz projeye bir Trigger ekleyelim. Bunun için projenize sağ tuş ile tıklayın ve Add i oradanda Triggeri tıklayın. Oluşturulacak .vb dosyasına, TriggerDeneme ismini verelim. Visual Studio bizim için aşağıdaki kodları oluşturuyor.

Imports System

Imports System.Data

Imports System.Data.Sql

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

 

Partial Public Class Triggers

    ' Enter existing table or view for the target and uncomment the attribute line

    ' <Microsoft.SqlServer.Server.SqlTrigger(Name:="TriggerDeneme", Target:="Table1", Event:="FOR UPDATE")> _

    Public Shared Sub  TriggerDeneme ()

        ' Add your code here

    End Sub

End Class

Oluşturulan Namespaceler ile ilgili daha çok bilgi edinmek isterseniz, CLR Destekli Stored Procedure Yazmak makaleme bakabilirsiniz. Oluşturulan Classın adı Trigger, shared sub ımıza dikkat edin, ismi TriggerDeneme yani benim Classıma verdiğim isim. Aslında dikkat edilecek nokta metodun üstündeki Attribute ler, hatırlarsanız Stored Procedure içinde bir attribute mantığı vardı, ancak Trigger da bu Attribute ler çok önemli, çünkü Triggeri genelde Tablo bazlı yazarız, ve bir olaya göre yazarız. Bu Attribute seti sizin Triggerinizin genel işlemlerini size gösteriyor, bize verilen şablonda ismi TriggerDeneme, Table1 Tablosu için oluşturulacak ve çalışacağı olay durumu UPDATE, nasıl daha çok şekillendirebileceğimizi makalenin ileriki bölümlerinde göreceğiz. Şimdi çok basit olarak bir Trigger oluşturalım ve çalışma mantığını görelim. Aşağıdaki kodları sizde kendi projenizde yazın.

Imports System

Imports System.Data

Imports System.Data.Sql

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

 

Partial Public Class Triggers

    <Microsoft.SqlServer.Server.SqlTrigger(Name:="TriggerDeneme", Target:="dbo.Users", Event:="FOR INSERT")> _

    Public Shared Sub TriggerDeneme()

        SqlContext.Pipe.Send("Insert icin calisti")

    End Sub

End Class

İsmini aynı olarak bıraktım ancak çalışacağı tablo olarak az önce oluşturduğumuz Users tablosunu seçtim ve INSERT işlemi için çalışmasını istedim. SqlContext.Pipe property sini önceki makalelerden hatırlayacaksınız, benim amacım Messages bölümünde bir mesaj göstermek, hatırlarsanız önceki kullanımlarımızda Pipe.Send() metoduna SqlDataReader yolluyorduk ve bu vasıtayla verileri görebiliyorduk. Eğer Send() metoduna bir String yollarsanız, yolladığınız String i Messages bölümünde gösterir. Triggerim şu anda hazır, ancak daha önceki makalelerdeki gibi Deploy yöntemi ile SQL Server a eklemeyeceğiz, bu işi yapmanın farklı iki yolu daha var, bunları göstereceğim. Bu işi yapmanın ilk yöntemi SQL Server Management Studio içinde yeni bir Query penceresi açın ve Database lerin listelendiği combo dan YazGelistirOrnek i seçin. İlk yapacağımız iş oluşan dll i yani assembly i SQL Serverin seçtiğimiz Database ine eklemek olacak, daha sonra içindeki Triggeri ekleyeceğiz. Bunun için şu formatta bir kod yazmalısınız.

CREATE ASSEMBLY <AssebmlyIsmi>

FROM <HarddisktekiYeri>

Bizim örneğimizdeki duruma uygun olarak şöyle yazmamız gerekiyor.

CREATE ASSEMBLY YazGelistirOrnek

FROM 'C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\YazGelistirOrnek\bin\YazGelistirOrnek.dll'

Eğer, Command(s) completed successfully mesajını aldıysanız Assembly niz o Database eklenmiş demektir. Şimdiki işimiz bu Assembly nin içindeki Triggeri eklemek. Bunun için ise yine Create komutunu kullanacağız ancak bu Trigger oluşturacağımız için ona göre yazacağız. Aşağıda bununla ilgili şablon kodu bulabilirsiniz.

CREATE TRIGGER <triggerinIsmi>

ON <tablonunIsmi>

FOR <INSERT,UPDATE,DELETE>

AS EXTERNAL NAME

<AssemblyIsmi>.[<NameSpace.ClassIsmi>].<FonksiyonunuzunIsmi>

Gördüğünüz gibi çok karmaşık değil, AS deyimine kadar olan bölüm aslında standart bir Trigger tanımlaması ancak AS bölümünden sonra yeni bir deyim olan EXTERNAL NAME deyimi geliyor. Bu deyim ile ekleyeceğimiz yapı hangi yapı ise, onun Assembly içindeki yerini anlatıyoruz, bu mantık biraz kafa karıştırıcı olabilir. AssemblyIsmi, dediğim bölümde sizin CREATE ASSEMBLY diyerek eklediğiniz isim, NameSpace.ClassIsmi dediğim bölüm ise siz sonradan değiştirmediyseniz, Projenizin isminden sonra Visual Studio nun oluşturduğu class ismi (ki genelde Triggers dır) ancak dikkat edin içinde nokta olduğu için bu bölümü köşeli parantezler içine almalısınız, yoksa hata alacaksınız. Son bölüm ise prosedürünüzün ismi olacak. Şimdi bizim örneğimiz için bu tanımlamanın nasıl yapılacağına bakalım.

CREATE TRIGGER Triggerim

ON dbo.USERS

FOR INSERT

AS EXTERNAL NAME

YazGelistirOrnek.[YazGelistirOrnek.Triggers].TriggerDeneme

Her nekadar prosedürümün ismi TriggerDenemede olsa ben SQL Server tarafından Triggerim olarak tanınmasını istedim. Users tablosu için çalışsın ve sadece INSERT ler için çalışsın dedim (haklısınız metodun başındada Attribute ilede bazı tanımlamalar yapmıştım, peki o niyeydi ? makalenin biraz daha ileriki bölümlerinde bahsedeceğim.)

Burada yeri gelmişken hemen bir Stored Procedureyi nasıl ekleyeceğinizide belirteyim. Mantığı aynı Trigger gibi, yani bir CREATE deyimi sonrada EXTERNAL NAME deyimi ile devam ediyor. Daha öncedende yaptığımız gibi ilk önce şablon yazılışına bakalım sonrada bir tane tanımlanmasını inceleyelim. Şablon yazılımı aşağıdaki şekilde,

CREATE PROCEDURE <SPninIsmi> <parametreler>

AS EXTERNAL NAME

<AssemblyIsmi>.[<NameSpace.ClassIsmi>].<FonksiyonunuzunIsmi>

Yine CREATE deyimi, ancak onu takip eden bu sefer PROCEDURE daha sonra Stored Procedurenizin ismi, eğer varsa parametreleri. Daha sonra AS EXTERNAL NAME ve yukarıda bildiğiniz yapı. Assembly i ekledikten sonra içindeki birden çok Stored Procedure ve Triggeri bu yöntemle ekleyebilirsiniz. Şimdi gerçek bir tanımlamayı nasıl yapacağımıza bakalım.

CREATE PROC GetPersonAddressType AS

EXTERNAL NAME

YazGelistirOrnek.[YazGelistirOrnek.StoredProcedures].GetPersonAddressType

Kaldığımız yerden devam edelim, Şimdi SQL Server Management Studio ile bir bakalım az önce eklediğimiz Trigger nasıl gözüküyor.

 

Peki şimdi bir Insert işlemi yapalım ve sonucu görelim.

 

Gördüğünüz gibi Pipe.Send() ile yolladığım mesaj geldi.

Peki şimdi o attributelerin ne işe yaradığını inceleyelim. Eğer CLR Destekli Stored Procedure Yazmak başlıklı makaleleri okuduysanız, orada Assembly i ve Proseduru elle tanımlamadığımı, Visual Studio dan Deploy ettiğimi hatırlayacaksınız, eğer sizde bu şekilde uğraşmak yerine Visual Studiodan Deploy etmek isterseniz o Attribute de tanımladığım değerlere göre metotlarımız çalışacak. Yani siz Attribute leri tanımlamayada bilirsiniz, ancak bu sefer o yapının bu tür özelliklerini (örneğin ismi, hangi tabloya bağlı olacağını) sizin tanımlamanız gerekecek.

Hızlıca neler yaptığımıza bir göz atalım, ilk önce Assembly i ekledik ve daha sonra Triggerimizi ekledik. Siz bu yapıları silmek isterseniz, direk Assembly i silemezsiniz. Çünkü Triggeriniz bu Assembly ile çalışıyor, bu yüzden ilk önce bu Assembly nin içinde SQL Server da bulunan yapıları silmelisiniz, bunun için Assembly nize sağ tuş ile tıklayıp, View Dependencies i kullanabilirsiniz, ilk önce Triggerimizi Drop edelim.

DROP TRIGGER Triggerim

Sonrada Assembly mizi Drop edelim.

DROP ASSEMBLY YazGelistirOrnek

Eğer bir değişiklik yapmak isterseniz, nasıl CREATE ve DROP komutları varsa, aynı zamanda ALTER komutunuda kullanabilirsiniz.

Bu makalemizde, CLR Destekli Trigger yazmak ile uğraştık, bunun için çok basit olarak standart bir Trigger yazdık ve nasıl çalıştığını inceledik. Ancak dahada önemli olan bir noktayıda irdeledik, Visual Studio dan Deploy etmeden, bir Assembly i eklemek ve daha sonra bu eklediğimiz Assembly nin içinde Triggeri tanımlayıp kullanmak.

Bir sonraki makalede Triggerlar ile ilgili daha gelişmiş örnekler yapıp, INSERTED ve UPDATED tablolarını nasıl kullanacağımızı, bir problem olduğunda nasıl Rollback yapacağınızı ve .Net destekli Trigger yazmanın diğer avantajlarını inceleyeceğiz.

İstek, öneri ve eleştirileriniz için aşağıdaki mailimi kullanabilirsiniz.

Levent Cenk ÇAĞLAR

cenk.caglar@yazgelistir.com