SQL Server 2005 Clr Destekli Trigger Yazmak (1)

[ReKoBaa_61]

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:
_
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 [img]images/smilies/smile.gif[/img]

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:
_
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:
_
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:
_
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\YazGelistirOrne k.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.