Makale Özeti

CLR Destekli Trigger yazmayı incelediğimiz ilk makalenin daha gelişmiş örnekleri olan ve daha gelişmiş konulara deyinen ikinci bölümü

Makale

CLR Destekli Trigger Yazmak - II

 

Merhabalar, Bir önceki makalemiz olan CLR Destekli Trigger Yazmakda, basit bir Trigger yazıp, bu Triggeri SQL Server a nasıl ekleyeceğimizi görmüştük, şimdi ise daha karmaşık örnekler yapacağız ve Trigger yazmayı biraz daha pekiştireceğiz, bunun için daha önce kullanmış olduğum Visual Studio Projesine devam edeceğim. Sizede devam etmenizi tavsiye ederim.

İlk örneğimizde inceleyeceğimiz yapı, SqlTriggerContext yapısı, bu yapı sayesinde Trigger lara özel işlemler yapabiliyoruz (örneğin; Triggerda hangi işlem yapılmış, hangi sütunlar UPDATE edilmiş gibi) hızlıca örneğimizi inceleyelim.

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

    Public Shared Sub AddNewUser()

        Dim ctx As SqlTriggerContext = SqlContext.TriggerContext

        If ctx.TriggerAction = TriggerAction.Insert Then

            Dim msg As String = ""

            For i As Integer = 0 To ctx.ColumnCount - 1

                msg += i + 1 & ". Kolon "

                If ctx.IsUpdatedColumn(i) Then

                    msg += "Guncellenmis"

                Else

                    msg += "Guncellenmemis"

                End If

                msg += Environment.NewLine

            Next

            SqlContext.Pipe.Send(msg)

        End If

    End Sub

Yukarıdaki kodumuzdaki Trigger, Insert işlemleri için çalışacak ve Users tablosunu dinleyecek, Triggerimizin ve Metodumuzun adı AddNewUser. Bir tane SqlTriggerContext tanımlıyorum, bu Class bana Trigger ile ilgili yetenekleri getiriyor. Bu class ın Instancesini SqlContext class ının Shared metodu olan TriggerContext metodu ile alıyorum, bu metod bana o an çalışan deyim ile ilgili bilgileri getiriyor ve ben buna göre işlemlerimi yapıyorum, örneğin TriggerAction property sine bakıyorum, ben her ne kadar Attribute olarak yukarıda belirtmişsemde burada tekrar kontrol etkemk mantıklı, çünkü tanımlamanızı yaparken FOR INSERT, UPDATE şeklindede bir tanımlama yapabilirdiniz, bu gibi durumlarda, yani Triggerinizin birden çok olayı dinlediği zamanlarda o olayları kodunuzun içinde bu şekilde ayırabilirsiniz. TriggerAction property sine bir göz atın, ihtiyacınızdan fazla şey bulacaksınız :)

Daha sonra bir String tanımladım, böylece sonuçları SQL Server Management Studio dan görebileceğiz. For döngüsüne dikkatinizi çekmek isterim, i değişkenini tanımladım, ve 0 dan başlayıp, eklenen tabloldaki kolonların sayısının bir eksiği kadar dönmesini sağladım, IsUpdatedColumn metodu ise kolonların index numarasını alıyor ve sırası ile tek tek bütün kolonlarda dönmesini sağladım, eğer Kolon Update edilmişse doğru sonucunu vericek ve bunaradan oluşanların hepsinide msg değişkenine ekledim. Şimdi sonucu inceleyelim,

 

Insert yaptığımız için bütün kolonların Update edilğini söyledi, ki doğru, şimdi kodumuzu aşağıdaki şekilde değiştirelim ve UPDATE işlemi için bu kodun nasıl çalıştığına bakalım,

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

    Public Shared Sub UpdateUser()

        Dim ctx As SqlTriggerContext = SqlContext.TriggerContext

        If ctx.TriggerAction = TriggerAction.Update Then

            Dim msg As String = ""

            For i As Integer = 0 To ctx.ColumnCount - 1

                msg += i + 1 & ". Kolon "

                If ctx.IsUpdatedColumn(i) Then

                    msg += "Guncellenmis"

                Else

                    msg += "Guncellenmemis"

                End If

                msg += Environment.NewLine

            Next

            SqlContext.Pipe.Send(msg)

        End If

    End Sub

Dikkat edin olayı, FOR UPDATE olarak değiştirdim, metodun ismini UpdateUser yaptım ve TriggerAction Update mi diye baktım, şimdi bu kodun sonuçlarını inceleyelim,

 

Gördüğünüz gibi sonuç olarak 1. ve 3. kolon için güncellenmemiş, 2. kolon için güncellenmiş mesajını aldık. Ben ismi düzenlediğimden gerçekten 2. kolon güncellenmiş durumda.

 

Trigger larda çok sık kullanıdığımız, INSERTED ve DELETED tablolarını nasıl kullanacağımızı inceleyelim, aşağıdaki örnek Insert ve Update işlemlerinde çalışacak ve UserName sutununa girilen değerin 5 ile 20 karakter arasındamı diye kontrol edecek.

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

    Public Shared Sub AddAndUpdateCheckUserName()

        Dim ctx As SqlTriggerContext = SqlContext.TriggerContext

        If ctx.TriggerAction = TriggerAction.Insert Or ctx.TriggerAction = TriggerAction.Update Then

            Dim conn As SqlConnection = New SqlConnection("Context Connection=True")

            Dim comm As SqlCommand = New SqlCommand()

            comm.Connection = conn

            comm.CommandText = "SELECT * FROM INSERTED"

            conn.Open()

            Dim dr As SqlDataReader = comm.ExecuteReader()

            dr.Read()

            Dim userNameLenght As Integer = dr.GetString(1).Length

            If userNameLenght < 3 Or userNameLenght > 20 Then

                Throw New Exception("Invalid User Name")

            End If

        End If

    End Sub

Metodumuzun ismi, AddAndUpdateCheckUserName, Attribute deki Event a bakarsanız FOR INSERT, UPDATE için çalışacak, yine bir SqlTriggerContext tanımlıyorum ve TriggerAction özelliğine bakıyorum, Insert ve Update ise aşağıdaki bölümü çalıştırmasını sağlıyorum, daha sonra bir Connection ve Command tanımlıyorum, Command ın CommantText ine ise, SELECT * FROM INSERTED yani Insert edildiğinde temp olarak verilen yazıldığı tabloyu sorguluyorum, DataReader tanımlıyorum. 1 indexli sutun ile işlem yapacağım için onu if ile denetliyorum ve uzunluğuna bakıyorum, eğer uzunluğu 3 ten küçük ise ve 20 den büyük ise hata vermesini sağlıyorum (RollBack yapmak için bir Exception fırlatmak yeterli, böylece veri eklenmiyor) eğer benim istediğim koşullarda ise zaten If e takılmıyor ve verileri ekliyor.

 

Şimdi daha kolay olan başka bir örnek yapalım, aslında belkide hemen hemen her tablo için yapılması gereken bir işlem olan, 1 den çok silinmeyi engelleyek bir kod yazalım.

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

    Public Shared Sub DeleteUsers()

        Dim ctx As SqlTriggerContext = SqlContext.TriggerContext

        If ctx.TriggerAction = TriggerAction.Delete Then

            Dim conn As SqlConnection = New SqlConnection("Context Connection=True")

            Dim comm As SqlCommand = New SqlCommand()

            comm.Connection = conn

            comm.CommandText = "SELECT COUNT(*) FROM DELETED"

            conn.Open()

            If CInt(comm.ExecuteScalar) > 1 Then

                Throw New Exception("1 den fazla kayit silinemez")

            End If

        End If

    End Sub

DeleteUsers metodumuzda ise, Event olarak FOR DELETE i dinliyoruz, bu sefer DELETED tablosundaki eleman sayısına bakıyorum ve eğer birden fazlasaysa bir Exception fırlatıyorum, böylece bu tabloyu dışarıdan, bir şekilde hacklenip, tablodaki bütün verilerin bir anda silinmesini engelliyorum.

 

Şimdi dahada gelişmiş bir örnek yapalım, bu örneğimiz Database seviyesinde bir trigger, yani veritabanının şemasına yapılan değişiklikleri dinliyor, örneğin bir Tablo yaratmak gibi, bizim örneğimizde bir tablo değiştirilmiş (Alter) ise, yada silinmiş (Drop) ise çalışacak ve gerekli bilgiyi bize Loglayacak ve işlem geri alacak, böylece tablolarımızı daha güvenli hale getirmiş olabileceğiz. Ancak öncelikle şunu bilmek gerekiyor, Database seviyesinde bir Triggeri Visual Studio 2005 ile Deploy edemiyorsunuz, bunun için kodunuzu yazdıktan sonra elle (CLR Destekli Trigger Yazmak – I makalesinde anlatıldığı gibi) eklemeniz gerekiyor, farkını göreceksiniz. Şimdi aşağıdaki kodu inceleyiniz.

    Public Shared Sub AlterOrDropTable()

        Dim ctx As SqlTriggerContext = SqlContext.TriggerContext

        If ctx.TriggerAction = TriggerAction.AlterTable Or ctx.TriggerAction = TriggerAction.DropTable Then

            LogToText(ctx.EventData.Value)

            Throw New Exception("Degisiklik yapamazsiniz")

        End If

    End Sub

    Public Shared Sub LogToText(ByVal parErrorMessages As String)

        Dim fileName As String = "C:\logs.dat"

        Dim fs As System.IO.FileStream = New System.IO.FileStream(fileName, System.IO.FileMode.Append, System.IO.FileAccess.Write)

        Dim sw As System.IO.StreamWriter = New System.IO.StreamWriter(fs)

        sw.WriteLine(Environment.NewLine & "--------------------------")

        sw.Write("Log Message : " & parErrorMessages)

        sw.WriteLine(Environment.NewLine & "--------------------------")

        sw.Flush()

        sw.Close()

    End Sub

Bu yazdığımız kodda Attribute yok, az öncede anlattığım gibi bu kodu elle tanımlayacağız, kodumuzda loglama işinide yaptığımız için, yani diske eriştiğimiz için assembly mizi UNSAFE olarak tanımlayacağız. Yazdığımız Trigger AlterTable ve DropTable ı dinliyor ve eğer yapılan işlem bu iki komuttan biriyse, çalışıp, komutu loglayıp, işlemi iptal edecek ve tabloların silinmesini yada değiştirilmesini engelleyecek, loglayan bölümü ayrı bir fonksiyon haline yazdım, .Net te yazılmış diğer herhangi bir fonksiyonuda Triggerde kullanabiliceğim. Bu yazdığımız Triggeri eklemek için ilk önce Assembly mizi UNSAFE olarak ekleyelim,

CREATE ASSEMBLY YazGelistirOrnek

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

WITH PERMISSION_SET = UNSAFE

Daha sonrada Triggeri ekleyen komutumuzu yazalım,

CREATE TRIGGER AlterOrDropTable

ON DATABASE FOR DROP_TABLE, ALTER_TABLE

AS EXTERNAL NAME YazGelistirOrnek.[YazGelistirOrnek.Triggers].AlterOrDropTable

Bu örnekte SqlTriggerContext in EventData.Value Property sini kullandım, Property geriye string döndürüyor ancak gelen data XML olarak geliyor, siz bir XMLDocument tanımlayıp, ihtiyacınız olan bölümleri loglayabilirsiniz,

 

Log dosyamız şöyle gözükecek,

 

Bu iki makalemizde, Kapsamlı olarak Triggerleri CLR destekli olarak nasıl kullanacağınızı inceledik. Bir sonraki makalemiz CLR Destekli User Defined Type yazmak olacak.

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

Levent Cenk ÇAĞLAR

cenk.caglar@yazgelistir.com