SQL Server Trigger Çeşitleri

20 Ara by NURULLAH ÇAKIR

SQL Server Trigger Çeşitleri

Trigger veritabanı sunucusunda bir olay gerçekleştiğinde başka bir olayı tetiklemek için kullanılır. DML,DDL ve Logon Trigger olmak üzere 3 tip trigger vardır.

  1. DML(Data Manipulation Language) Trigger
  2. DDL(Data Definition Language) Trigger
  3. Logon Trigger

 

DML(Data Manipulation Language) Trigger: Veritabanına yapılan INSERT,UPDATE ve DELETE işlemlerini içerir. DML Trigger’ın yapacağı işi PRIMARY KEY, FOREIGN KEY, UNIQUE constraint ya da CHECK CONSTRAINTS’ler ile de yapabilirsiniz.  “Primary Key ve Foreign Key“,”Primary Key ve Unique Constaint’in farkları” ve “CHECK CONSTRAINT” isimli makalelerimde bu kavramlar hakkında bilgi bulabilirsiniz. DML Trigger’ları, yukarıda bahsedilen CONSTRAINT’ler fonksiyonel ihtiyaçları karşılamadığında kullanabilirsiniz.

 

DML trigger’larının aşağıdaki örneklerde kullanılacak inserted ve deleted isimli tabloları vardır. Örneğin bir insert işlemi geldiğinde inserted tablosunda insert edilen kayıtları, bir update ya da delete işlemi geldiğinde deleted tablosunda da silinen kayıtları bulabileceksiniz.

 

2 tip DML Trigger vardır.

 

  1. AFTER ya da FOR: Veritabanına yapılan herhangi bir DML işleminin(INSERT,UPDATE ve DELETE) tamamlanmasından sonra tetiklenir. SQL Server’ın önceki sürümlerinde FOR olarak geçiyordu bu yüzden hala FOR desteği devam ediyor. AFTER ve FOR aynı anlama geliyor. AFTER INSERT,AFTER UPDATE ve AFTER DELETE için birer örnek yapalım. Yapacağımız her örnek için aşağıda create script’i olan tabloyu kullanacağız.

 

  • AFTER INSERT: Yukarıdaki TriggerOrnegi tablomuza yapılan insert işlemlerini başka bir tabloya aktararak bu tabloyu yedek olarak tutma amacıyla bir AFTER INSERT Trigger’ı yazalım. Bu trigger TriggerOrnegi tablosuna yapılan her insert’ü aşağıda oluşturduğumuz TriggerOrnegiBackup isimli tabloya da uygulasın.

 

 

Trigger’ı da aşağıdaki şekilde oluşturalım.

 

Şimdi TriggerOrnegi isimli tablomuza aşağıdaki script yardımıyla bir insert gerçekleştirelim. Ardından da TriggerOrnegi ve TriggerOrnegiBackup tabloları select çekerek trigger’ımızın çalışıp çalışmadığını kontrol edelim.

 

Aşağıdaki ekran görüntüsünden de görebileceğiniz üzere trigger başarılı bir şekilde çalıştı.

  • AFTER UPDATE: TriggerOrnegi isimli tablomuzda bir update olduğunda bu update’in TriggerOrnegiBackup isimli tabloyada yansıması için after update trigger’ı kullanalım. Aşağıdaki trigger, TriggerOrnegi isimli tablonun AdSoyad kolonunda bir update işlemi gerçekleştiğinde TriggerOrnegiBackup isimli tablonun AdSoyad kolonunuda update ediyor.

 

Trigger’ın doğru çalıştığını kontrol etmek için TriggerOrnegi tablosundaki AdSoyad kolonunu update eden ve sonra her iki tablodan da select çeken aşağıdaki sorguyu çalıştıralım.

 

 

Gördüğünüz gibi trigger doğru bir şekilde çalıştı.

  • AFTER DELETE: Aynı şekilde ilk tabloda bir silme işlemi olduğunda ikinci tablodan da aynı silme işlemini gerçekleştirelim. Aşağıdaki şekilde AFTER DELETE trigger’ını oluşturalım.

 

 

TriggerOrnegi tablomuzdan aşağıdaki script yardımıyla bir kayıt silerek trigger’ın çalışıp çalışmadığını kontrol edelim.

 

Gördüğünüz gibi TriggerOrnegi tablomuzdan bir kayıt sildiğimizde ilgili kayıt TriggerOrnegiBackup tablosundan da silindi.

  1. INSTEAD OF: AFTER trigger’ında DML işlemleri başarılı bir şekilde gerçekleştikten sonra trigger çalışıyordu. INSTEAD OF trigger’ında ise DML işlemi gerçekleşmeden bu trigger tetiklenir. Bir çok amaçla bu trigger’ı kullanabilirsiniz biz bu makalede tek bir amacı benimseyen örnekler yapacağız. INSTEAD OF INSERT, INSTEAD OF UPDATE ve INSTEAD OF DELETE trigger çeşitlerinin nasıl oluştuğuna bakalım.

 

  • INSTEAD OF INSERT: INSTEAD OF INSERT ile Insert işlemi ilk tabloya gerçekleşmeden önce bu insert yerine şu işlemi yap diyebiliyoruz. TriggerOrnegi isimli tablomuza yapılan bir insert işleminin TriggerOrnegi tablosuna, insert edilen ID değerini 1 arttırarak kaydedelim. TriggerOrnegiBackup isimli tabloya da aynı şekilde ID değerini 1 arttırarak insert yapılmasını sağlayalım. Aşağıdaki script yardımıyla trigger’ımızı oluşturuyoruz.

 

 

 

Aşağıdaki script yardımıyla da TriggerOrnegi isimli tabloya insert işlemi yapıp iki tabloyada select çekerek sonucunu görelim. (Öncesinde AFTER örneğinde oluşturduğumuz tüm trigger’ları ve kayıtları silmeyi unutmayın.)

 

 

 

Gördüğünüz gibi ID değerini 1 olarak insert etmemize rağmen değerini bir arttırıp 2 yaptık ve iki tabloya da bu şekilde insert işlemini gerçekleştirdik.

 

 

  • INSTEAD OF UPDATE: Update statement’ı çalıştığında bu ifade yerine çalışacak statement’ları çalıştırır. Örneğimizi uygulamak için öncelikle aşağıdaki script ile bir log tablosu oluşturalım.

 

 

Oluşturacağımız Trigger,TriggerOrnegi isimli tablomuzda ID kolonuna update gelirse update’i rollback yapacak ve yukarıda oluşturduğumuz TriggerLog tablosuna da değiştirilmeye çalışılan ID değerini ve açıklamasını girecek. AdSoyad kolonuna update gelirse de bu update değerini gerçekleştirecek ve hangi kaydın update edildiğini açıklaması ile beraber TriggerLog tablosuna girecek. Oluşturacağımız trigger’ın create script’i aşağıdaki gibidir.

 

 

Şimdi ilk script’imizde TriggerOrnegi tablosundaki ID kolonunu update etmeye çalışalım ve TriggerOrnegi ve TriggerLog tablolarına select çekerek trigger’ın çalışıp çalışmadığını kontrol edelim. Önceki trigger’ları silmeyi unutmayın.

Yukarıdaki script’i çalıştırdığımızda aşağıdaki gibi ID değerinin update edilemeyeceğini ve update işleminin rollback olduğunu belirten bir mesaj aldık.

 

Messages kısmından hemen solundaki sekmedeki Result sekmesine geçtiğimizde de kaydın update olmadığını ve hangi değere update edilmek istendiği bilgisini TriggerLog tablosunda görebiliriz.

 

Şimdi AdSoyad kolonunu aşağıdaki script yardımıyla update etmeye çalışalım.

 

 

Aşağıda gördüğünüz gibi update işlemi gerçekleşti ve daha sonra TriggerLog tablosuna ilgili Log kaydı düşüldü.

 

 

  • INSTEAD OF DELETE: Delete statement’ı çalıştığında bu ifade yerine çalışacak statement’ları çalıştırır. Tabloda ID değeri 2 olan kaydın Nurullah ÇAKIR’a ait olduğunu biliyoruz. Eğer ID değeri 2 ise silme işlemine izin vermeyen ve Nurullah ÇAKIR isimli kaydın silinemeyeceğine dair bir uyarı veren ve Nurullah ÇAKIR kişisine ait kaydın silinmek istendiğine dair TriggerLog tablosuna kayıt düşen bir trigger oluşturalım. Tabi kaydın ID değeri 2 değilse silme işlemini de gerçekleştirsin.

 

 

Nurullah ÇAKIR isimli kişiye ait ID değeri 2 olan kaydı silmeye çalışalım. Tabi önce diğer trigger’ları silmeyi unutmayın.

Script’i çalıştırdığımızda bize aşağıdaki gibi bir mesaj verdi.

Results sekmesine geçtiğimizde de kaydın silinmediğini ve Log tablosuna da ilgili log kaydının düştüğünü görebiliriz. Eğer ID değeri 2’nin dışındaki bir kayıt silinmek isteseydi silme işlemi gerçekleşecekti.

 

 

DDL(Data Definition Language) Trigger:  Veritabanına yapılan modifikasyon işlemleri gerçekleştiğinde tetiklenebilir. Hangi işlemden sonra tetikleneceğini Trigger’ı oluştururken belirtiriz. Veritabanı modifikasyon işlemlerine aşağıda birkaç örnek verdim.

  • CREATE DATABASE, DROP DATABASE
  • Create Table, Alter Table, Drop Table
  • Create Function,Alter Function,Drop Function
  • Create View,Alter View,Drop View
  • Create Stored Procedure,Alter Stored Procedure, Drop Stored Procedure

 

Veritabanı içindeki modifikasyon işlemlerini içeren bir  örnek yapmak için Test veritabanımızda aşağıdaki script yardımıyla bir stored procedure, bir view, bir function oluşturalım.

 

 

 

Ornek objelerimizi oluşturduktan sonra aşağıdaki gibi bir Database Trigger oluşturalım.

Tanımlayacağımız trigger bu veritabanında TABLE,FUNCTION,VIEW ya da STORED PROCEDURE oluşmasını ve mevcut olanlarında  silinmesini ya da değiştirilmesini engelleyecek.

 

 

Trigger’ı oluşturduktan sonra yukarıda bahsedilen objeleri silmeyi ya da değiştirmeyi ya da yeni bir tane oluşturmayı deneyerek sonucu görebilirsiniz.

Örnek olarak oluşturduğumuz view’i silmeyi deneyelim.

DDL Trigger ile neleri kontrol edebileceğiniz öğrenmek için aşağıdaki script’i çalıştırmalısınız.

 

Logon Trigger: Kullanıcı login olmak için giriş yaptığında login ve şifre bilgileri kontrol edildikten sonra eğer bağlantı bilgileri doğruysa bağlantı gerçekleşmeden tetiklenir. Başarısız bağlantılar için bu yapıyı kullanamazsınız. Bunun yerine SQL Server Alertleri kullanmalısınız.

Failed Logon’ları Mail Atacak Alert Oluşturmak” isimli makalemde bu konuyu anlattım. “Always On Alert Sistemi” ve “Yeni Kurulumda yapılması gereken konfigurasyonlar” isimli makalelerimde alertler ile ilgili daha detay bilgi bulabilirsiniz.

Başarılı girişleri loglamak ya da bazı loginlerin yetkilerini sınırlandırmak amaçlı kullanılabilir. Bu iki konuyla ilgili 2 tane örnek yapalım.

Örnek1:

Aşağıdaki script ile master veritabanında başarılı loginleri tutmak için bir tablo oluşturuyoruz ve Logon Trigger ile başarılı girişleri logluyoruz. 

Çok fazla login işlemi master veritabanınızı şişirebilir. Bu yüzden başka bir veritabanına kaydetmeniz ya da bu tabloda eskiyen kayıtları silmeniz gerekebilir.

 

Logon Trigger’ı aşağıdaki script yardımıyla silebilirsiniz.

 

Örnek2:

Aşağıdaki script ile TestLogin isimli bir login oluşturuyoruz ve bu login’in session sayısı 3 olduğunda trigger’ın tetiklenerek 3 den fazla connection açmasını engellemiş oluyoruz.

 

TestLogin ile login olduktan sonra new query diyerek birkaç tane query ekranı açmaya çalışın. Üçüncü query ekranını açmaya çalıştığınızda aşağıdaki gibi hata verecektir.

Logon Trigger’ı aşağıdaki script yardımıyla silebilirsiniz.

 

DROP TRIGGER Trigger_ConnectionLimit ON ALL SERVER

 

Tablo seviyesinde Trigger’ları aşağıdaki gibi Triggers altında görebilirsiniz.

 

 

Veritabanı seviyesindeki Trigger’ları aşağıdaki gibi Programmability->Database Triggers altından görebilirsiniz.

 

 

Server Seviyesindeki Trigger’ları aşağıdaki gibi Server Objects’in altından görebilirsiniz.

 

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir

Lütfen captcha kodunu giriniz *

Lütfen Resimdeki Kodu Boşluğa Giriniz.