Sql Server 2005 Clr Destekli Trigger Yazmak

delphiform · 25-01-06, 10:27

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.

Kod:

    <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,

Kod:

    <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.

Kod:

    <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.

Kod:

    <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.

Kod:

    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,

Kod:

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,

makalemiz yazgelistir.com adresinden alınmıştır.

25-01-06, 10:27	#1
delphiform Meraklı Giriş Tarihi: 24-01-2006 Yer: ' im TÜRKİYENİN BaŞkEnTiNdEn Yaş: 29 Mesajlar: 258	Sql Server 2005 Clr Destekli Trigger Yazmak 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. Kod: <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, Kod: <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. Kod: <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. Kod: <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. Kod: 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, Kod: 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, makalemiz yazgelistir.com adresinden alınmıştır.

Konu Araçları
Çıktı Görüntüsünü Göster Sayfayı E-posta İle Yolla