Trigger_1
1 Ocak 2017 Pazar
Trigger'lar özelleşmiş bir tür stored prosedürlerdir(SP). SP'lerden farkı biz istediğimizde değil bir tabloda değişiklik olduğunda devreye girmeleridir. Dışardan herhangi bir parametre almazlar ve değer döndürmezler.
Trigger'lar tablolar üzerinde tanımlanırlar. Tanımlandıktan sonra ilgili tabloda meydana gelen veri değişikliğinin(INSERT, UPDATE, DELETE) hemen ardından tepki verirler. Bu veri değişikliklerini SQL Server sözde tablo(pseudo table) olan Inserted ve Deleted tabloları aracılığıyla işaretler.
SQL Server trigger'ları 4 'e ayrılır:
1. DML(Data Manipulation Language) Triggers
2. DDL(Data Definition Language) Triggers
3. CLR(Common Language Runtime) Triggers
4. Logon Triggers
DML trigger'ları adı üzerinde DML(Insert, update, delete) işlemlerinden hemen sonra devreye girerler.
DML trigger'ları da kendi içinde 2'ye ayrılır:
1. After Triggers (For trigger olarak da geçer),
2. Instead of Triggers
Not: FOR trigger ile AFTER trigger tamamen aynı anlama gelmekle birlikte SQL Server'ın eski sürümlerine uyumluluğun devam ettirilmesi açısından desteklenmeye devam ediyor. Ancak bir seçim yapacak iseniz bu seçimi AFTER'dan yana kullanmanız tavsiye edilir çünkü standart bir yöntem olarak kabul ediliyor. (back-compability)
Konunun pekişmesi adına DML trigger'la alakalı örnekler yapalım dilerseniz:
Örnek senaryomuz şöyle olacak, tblSiparis ve tblUrun(yada tblStok) adında 2 tablomuz var. tblSiparis tablomda ürün siparişi geçtiğimde stoğumdaki ürün adedinde de buna göre değişim gözlenmesini bekliyorum. Bu işlemleri trigger yardımıyla yapacağız.
Çok basit seviyede Resim-1'de de görmüş olduğunuz gibi 2 adet tablo oluşturdum. Şimdi, senaryoma göre tblSiparis tabloma ürün siparişi girdiğimde tblUrun tablomdaki girmiş olduğum ürün adedinde de buna göre azalma olması gerekiyor. Vakit kaybetmeden ilgili (insert) trigger'ımızı yazalım;
CREATE TRIGGER trg_tblSiparis_ForInsert
ON tblSiparis
FOR INSERT
AS
DECLARE @UrunID AS INT;
DECLARE @SiparisAdet AS INT;
DECLARE @KalanMiktar AS INT;
DECLARE @ToplamMiktar AS INT;
SELECT @SiparisAdet= i.SiparisAdet FROM inserted i;
SELECT @UrunID = i.UrunID FROM inserted i;
SELECT @KalanMiktar= UrunAdet FROM tblUrun WHERE UrunID=@UrunID;
SELECT @ToplamMiktar=@KalanMiktar-@SiparisAdet
BEGIN
UPDATE tblUrun SET UrunAdet=@ToplamMiktar WHERE UrunID=@UrunID
END
Not: Şayet bir trigger aktif/pasif yapmak istiyorsanız şu kod bloğunu yazmalısınız:
DISABLE TRIGGER trg_tblSiparis_ForInsert ON tblSiparis
ENABLE TRIGGER trg_tblSiparis_ForInsert ON tblSiparis
Bu insert trigger'ı yeni bir satır eklediğimizde devreye girecektir. Şimdi yazacağımız trigger ise eklemiş olduğumuz satırda güncelleme yaptığımızda devreye girecektir.
CREATE TRIGGER trg_tblSiparis_ForUpdate
ON tblSiparis
FOR UPDATE
AS
BEGIN
DECLARE @YeniSatisAdedi int, @SatisAdedi int, @UrunID int
SELECT @SatisAdedi=SiparisAdet, @UrunID=UrunID FROM deleted
SELECT @YeniSatisAdedi=SiparisAdet, @UrunID=UrunID FROM inserted
if(@YeniSatisAdedi<@SatisAdedi)
BEGIN
UPDATE tblUrun SET UrunAdet=UrunAdet+(@SatisAdedi-@YeniSatisAdedi) WHERE UrunID=@UrunID
END
ELSE
BEGIN
UPDATE tblUrun SET UrunAdet=UrunAdet-(@YeniSatisAdedi-@SatisAdedi) WHERE UrunID=@UrunID
END
END
Devam ediyoruz.. Şimdi yazacağımız trigger eklenmiş olan siparişin silinmesi üzerinden gerçeklenecek. Yani delete trigger..
CREATE TRIGGER trg_tblSiparis_ForDelete
ON tblSiparis
FOR DELETE
AS
UPDATE tblUrun SET UrunAdet=u.UrunAdet+d.SiparisAdet
FROM tblUrun u JOIN deleted d ON u.UrunID=d.UrunID
Trigger oluştururken isimlendirme önemlidir. Ne tür trigger oluşturduğunuzu(insert, delete, update) isimlendirme esnasında belirtmeniz size daha sonra oluşturacağınız onlarca trigger arasından kolayca seçme şansı verecektir.
Şimdi bir de olayı tersinden okuyalım ve şöyle bir DELETE trigger yazalım. Diyelim ki, bir ürün üzerinden sipariş verdik ve o ürünü silmek istiyoruz. Hal böyle olunca sistem bizi uyarmalı ve siparişi olan bir ürünü silmek istediğimize dair bir mesaj ile bizi uyarmalı.
CREATE TRIGGER trg_UrunSil ON tblUrun
AFTER DELETE
AS
BEGIN
DECLARE @UrunID INT
SELECT @UrunID=UrunID FROM deleted
IF EXISTS(SELECT * FROM tblSiparis WHERE UrunID=@UrunID)
BEGIN
Print 'Bu ürün silinemez çünkü siparişi mevcut !'
ROLLBACK TRANSACTION
RETURN
END
END
Trigger'ları log tablosu oluşturma amacıyla da kullanabiliriz.
Örnek senaryom gereği tblUrun tabloma yeni bir ürün eklediğimde bunu logTablo isimli tabloma, eklenen ürün id'si ve güncel tarih ile kaydedilmesini istiyorum. Hemen trigger'ımızı yazalım:
CREATE TRIGGER trg_tblsiparisLog_ForInsert
ON tblSiparis
FOR INSERT
AS
BEGIN
DECLARE @ID int
SELECT @ID= UrunID FROM inserted
INSERT INTO LogTablo
VALUES ('UrunID= '+CAST(@ID AS VARCHAR(4))+ ' olan ürün eklenmiştir. Tarih= ' + CAST(GETDATE() AS VARCHAR(20)))
END
Diğer farklı DML trigger'lar için de yapı bu şekilde olacaktır, sadece FOR INSERT yerine FOR DELETE ve inserted yerine de deleted gelecektir.
Şimdi FOR UPDATE trigger için bir log tablo oluşturalım.
<a href="Images/50_ForUpdate.jpg" target="_blank"> <img src="Images/50_ForUpdate.jpg" height="400" width="500""> </a>
Resim-2'deki tblEmployees tablosunu baz alarak güncelleme durumu için tblLog tablosunda değişiklikleri tutacağız. İlgili trigger kodları aşağıdaki gibi olacaktır.
CREATE TRIGGER trg_tblEmployees_ForUpdate
ON tblEmployees
FOR UPDATE
AS
BEGIN
DECLARE @ID int
DECLARE @OldName NVARCHAR(25), @NewName NVARCHAR(25)
DECLARE @OldGender NVARCHAR(10), @NewGender NVARCHAR(10)
DECLARE @OldCity NVARCHAR(25), @NewCity NVARCHAR(25)
DECLARE @OldSalary INT, @NewSalary INT
DECLARE @Description NVARCHAR(250)
SELECT * INTO #TempTable FROM inserted
WHILE(Exists(SELECT ID FROM #TempTable))
BEGIN
SET @Description=''
SELECT TOP 1 @ID = ID, @NewName=Name, @NewGender=Gender, @NewCity=City, @NewSalary=Salary FROM #TempTable
SELECT @OldName=Name, @OldGender=Gender, @OldCity=City, @OldSalary=Salary FROM deleted WHERE ID=@ID
SET @Description='Employee with ID= '+ CAST(@ID as nvarchar(4)) + ' changed'
IF(@OldName<>@NewName)
SET @Description= @Description+' NAME from ' + @OldName + ' to ' + @NewName
IF(@OldGender<>@NewGender)
SET @Description= @Description+' GENDER from ' + @OldGender + ' to ' + @NewGender
IF(@OldCity<>@NewCity)
SET @Description= @Description+' CITY from ' + @OldCity + ' to ' + @NewCity
IF(@OldSalary<>@NewSalary)
SET @Description= @Description+' SALARY from ' + CAST( @OldSalary as nvarchar(10)) + ' to ' + CAST(@NewSalary as nvarchar(10))
INSERT INTO tblLog VALUES(@Description)
DELETE FROM #TempTable WHERE ID=@ID
END
END
Trigger'ı çalıştırıp tblEmployees tablosunda değişiklik yaptığınızda bu değişikliklerin tblLog tablosuna da yansıdığını göreceksiniz.
Uygulama Örnek: Yukarıdaki tblEmployees tablosunu baz alarak, kişinin maaşı düşürülmeye çalışıldığında sistemin buna izin vermemesi şeklinde bir trigger yazınız. (Maaş artımında herhangi bir problem olması beklenmiyor.)
CREATE TRIGGER trg_MaasDusmesin_AfterUpdate
ON tblEmployees
AFTER UPDATE
AS
BEGIN
IF EXISTS
(
SELECT * FROM inserted i INNER JOIN deleted d ON d.ID=i.ID WHERE d.Salary>i.Salary
)
BEGIN
RAISERROR ('Maaş Düşürülemez', 10,1)
ROLLBACK
END
END
Bu örneği farklı senaryolar için de düşünebilirsiniz mesela ürün fiyatlarının sadece artırılabileceği ancak azaltılamayacağı bir uygulama gibi..
AFTER Trigger'lar farklı amaçlar için de kullanılır. Örneğin veri silinmesine önlem olması için kullanılabilir.
CREATE TRIGGER trg_SilmeyeOnlem ON tablonuz
AFTER DELETE
AS
ROLLBACK
PRINT 'Silme işlemi engellendi !'
Not: Tanımlı trigger'ları görmek istiyor isek sp_helptrigger tablo_adı şeklindeki sorguyla o tablo üzerindeki tüm trigger'ları sorgulayabiliriz. Şayet bu sorgulamayı dbo sahiplik ön ekiyle yapacaksanız ' ' tırnak içine yazmalısınız ancak çıplak haliyle tablo adını yazacaksanız tırnak içine gerek yoktur.
Bu serinin sonuna geldik. Devam eden serilerde görüşmek üzere..