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