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.

DML(Data Manipulation Language) Trigger
DDL(Data Definition Language) Trigger
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.

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.

CREATE TABLE [dbo].[TriggerOrnegi](
[ID] [int] NULL,
[AdSoyad] [varchar](100) NULL
) ON [PRIMARY]

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.

CREATE TABLE [dbo].[TriggerOrnegiBackup](
[ID] [int] NULL,
[AdSoyad] [varchar](100) NULL
) ON [PRIMARY]

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

CREATE TRIGGER Trg_After_Insert
ON [dbo].[TriggerOrnegi]
AFTER INSERT AS
BEGIN
INSERT INTO [dbo].[TriggerOrnegiBackup]
SELECT * FROM INSERTED
END

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

INSERT INTO [dbo].[TriggerOrnegi]([ID],[AdSoyad])VALUES(1,'Nurullah ÇAKIR')
GO
Select * FROM [dbo].[TriggerOrnegi]
GO
Select * FROM [dbo].[TriggerOrnegiBackup]

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.

CREATE TRIGGER Trg_After_Update
ON [dbo].[TriggerOrnegi]
AFTER UPDATE AS
BEGIN
UPDATE [dbo].[TriggerOrnegiBackup]
SET    [dbo].[TriggerOrnegiBackup].AdSoyad = inserted.AdSoyad
FROM   [dbo].[TriggerOrnegiBackup]
       JOIN inserted
         ON [dbo].[TriggerOrnegiBackup].ID = inserted.ID 
END

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.

UPDATE [dbo].[TriggerOrnegi]   SET [AdSoyad] = 'Nurullah ÇAKIR Kim?' WHERE AdSoyad='Nurullah ÇAKIR'
GO
Select * FROM [dbo].[TriggerOrnegi]
GO
Select * FROM [dbo].[TriggerOrnegiBackup]

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.

CREATE TRIGGER Trg_After_Delete
ON [dbo].[TriggerOrnegi]
AFTER DELETE AS
BEGIN
DELETE FROM [dbo].[TriggerOrnegiBackup] WHERE [dbo].[TriggerOrnegiBackup].ID IN (SELECT ID FROM DELETED)
END

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

DELETE FROM [dbo].[TriggerOrnegi]  WHERE [AdSoyad] = 'Nurullah ÇAKIR Kim?'
GO
Select * FROM [dbo].[TriggerOrnegi]
GO
Select * FROM [dbo].[TriggerOrnegiBackup]

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

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.

CREATE TRIGGER Trg_InsteadOf_Insert ON [dbo].[TriggerOrnegi]
INSTEAD OF INSERT
AS BEGIN
INSERT INTO [dbo].[TriggerOrnegi]([ID],[AdSoyad])Select ID+1,AdSoyad FROM inserted
INSERT INTO [dbo].[TriggerOrnegiBackup]([ID],[AdSoyad])Select ID+1,AdSoyad FROM inserted
END

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

INSERT INTO [dbo].[TriggerOrnegi]([ID],[AdSoyad])VALUES(1,'Nurullah ÇAKIR')
GO
Select * FROM [dbo].[TriggerOrnegi]
GO
Select * FROM [dbo].[TriggerOrnegiBackup]

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.

CREATE TABLE [dbo].[TriggerLog](
[IDMevcutHali] [int] NULL,
[IDUpdateEdilmekIstenenHali] [int] null,
[Aciklama] [varchar](500) NULL
) ON [PRIMARY]

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.

CREATE TRIGGER Trg_InsteadOf_Update
       ON [dbo].[TriggerOrnegi]
INSTEAD OF UPDATE
AS
BEGIN
       DECLARE @ID INT, @AdSoyad VARCHAR(100)
       SELECT @ID= INSERTED.ID,
              @AdSoyad = INSERTED.AdSoyad
       FROM INSERTED

       IF UPDATE(ID)
       BEGIN
              RAISERROR('ID Değeri Update Edilemez.', 16 ,1)
              ROLLBACK
              INSERT INTO [dbo].[TriggerLog]
              VALUES(@ID, 'ID değeri update edilemez.')
       END
       ELSE
       BEGIN
              UPDATE [dbo].[TriggerOrnegi]
              SET AdSoyad = @AdSoyad
              WHERE ID = @ID
              INSERT INTO [dbo].[TriggerLog]
              VALUES(@ID, 'AdSoyad kolonu update edildi.')
       END
END

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

UPDATE [dbo].[TriggerOrnegi] SET ID = 3 Where ID=2
GO
Select * FROM [dbo].[TriggerOrnegi]
GO
Select * FROM [dbo].[TriggerLog]

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.

UPDATE [dbo].[TriggerOrnegi] SET AdSoyad = 'Nurullah ÇAKIR Kim?' Where ID=2
GO
Select * FROM [dbo].[TriggerOrnegi]
GO
Select * FROM [dbo].[TriggerLog]

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.

CREATE TRIGGER [dbo].[Trg_InsteadOf_Delete]
       ON [dbo].[TriggerOrnegi]
INSTEAD OF DELETE
AS
BEGIN
       DECLARE @ID INT
       SELECT @ID = DELETED.ID      
       FROM DELETED
       IF @ID = 2
       BEGIN
              RAISERROR('Nurullah ÇAKIR kişisine ait kayıt silinemez.',16 ,1)
              ROLLBACK
              INSERT INTO [dbo].[TriggerLog]
              VALUES(@ID,NULL, 'Nurullah ÇAKIR kişisine ait kayıt silinmek istendi.')
       END
       ELSE
       BEGIN
              DELETE FROM [dbo].[TriggerOrnegi]
              WHERE ID = @ID
              INSERT INTO [dbo].[TriggerLog]
              VALUES(@ID,NULL, 'Instead Of Delete')
       END
END

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.

DELETE FROM [dbo].[TriggerOrnegi] Where ID=2
GO
Select * FROM [dbo].[TriggerOrnegi]
GO
Select * FROM [dbo].[TriggerLog]

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.

--Örnek Stored Procedure Oluşturuyoruz.
CREATE PROCEDURE OrnekStoredProcedure
AS
BEGIN
SET NOCOUNT ON;
END

--Örnek Function Oluşturuyoruz
CREATE FUNCTION OrnekFunction(@a int)
RETURNS TABLE
AS
RETURN (SELECT 0 AS dönecektablo)

--Örnek View Oluşturuyoruz
CREATE VIEW [dbo].[OrnekView]
AS
SELECT dbo.TriggerOrnegi.*
FROM     dbo.TriggerOrnegi

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.

CREATE TRIGGER DDLTriggerOrnek
ON DATABASE
FOR CREATE_TABLE,DROP_TABLE, ALTER_TABLE,
CREATE_FUNCTION,ALTER_FUNCTION,DROP_FUNCTION,
CREATE_VIEW,ALTER_VIEW,DROP_VIEW,
CREATE_PROCEDURE,ALTER_PROCEDURE,DROP_PROCEDURE AS
PRINT 'Bu Veritabanında Modifikasyon İşlemleri Yapılamaz'
ROLLBACK

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.

DROP VIEW [dbo].[OrnekView]
GO

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

select * from sys.trigger_event_types

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.

CREATE TABLE Login_Info
(
       Login_Name           NVARCHAR(256),
       Login_Time           DATETIME,
       Host_Name            NVARCHAR(128)
)
CREATE TRIGGER Trigger_Log
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
       INSERT INTO master..Login_Info
       SELECT ORIGINAL_LOGIN(), GETDATE(),
       EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','NVARCHAR(128)')
END

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

DROP TRIGGER Trigger_Log ON ALL SERVER

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

USE master; 
GO 
CREATE LOGIN TestLogin WITH PASSWORD = 'Test123'  
GO 
GRANT VIEW SERVER STATE TO TestLogin; 
GO 
CREATE TRIGGER Trigger_ConnectionLimit 
ON ALL SERVER
FOR LOGON 
AS 
BEGIN 
IF ORIGINAL_LOGIN()= 'TestLogin' AND 
    (SELECT COUNT(*) FROM sys.dm_exec_sessions 
            WHERE is_user_process = 1 AND 
                original_login_name = 'TestLogin') > 3 
    ROLLBACK; 
END;

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.