-- Genel, SQL Server, T-SQL

SQL Server – Trigger’lar

Trigger’lar (tetikleyiciler), veri ya da sistemle ilgili değişimlerde otomatik olarak tetiklenen Stored Procedure’lerdir. Trigger’ların Stored Procedure’lerden farkı; dışarıdan parametre almaması, dışarıya parametre göndermemesi ve bir kullanıcı tarafından değil, bir olay tarafından tetiklenmesidir.

Trigger’ları Anlamak

SQL Server’da verilerin yönetiminde neredeyse her şeyi geliştirici belirler ve yönetir. Bu şekilde verileri değiştirmek, silmek ve yeni veriler eklemek gibi işlemler gerçekleştirilir. Ancak bazen belirli işlemlerin otomatik olarak gerçekleştirilmesi gerekir. Örneğin; bir veri eklendiğinde, aynı ya da farklı bir tabloda, başka bir sütunun değerini değiştirmek gerekebilir. Bu tür durumlarda SQL Server’ın geliştirici tarafından çalıştırılan nesneler yerine, olay oluştuğu anda tetiklenerek otomatik olarak çalışacak nesnelere ihtiyaç duyulur. Bu tanım, nesne yönelimli programlama tecrübesi olan T-SQL geliştiricileri tarafından çabuk kavranabilecektir. Çünkü nesne yönelimli programlamada event (olay) kavramının veritabanı programlamadaki karşılığı trigger’lardır. Veritabanında bir işlem tarafından tetiklenmeler gerçekleştirmek için trigger’lar kullanılır.

Trigger’lar sadece INSERT, UPDATE, DELETE işlemlerinden sonra devreye girebilecek şekilde programlanabilirler. Çoklu tablo ilişkisi (Inner Join) bulunan view’lere veri ekleme, sadece trigger’lar ile mümkün olabilir. Aynı zamanda, INSERT, UPDATE, DELETE sorguları çalıştırıldığında, bu işlemlerin yerine yapılmak istenen farklı işlemler de trigger’lar ile gerçekleştirilebilir.

Trigger anlatımında sürekli işlemlerden sonra gerçekleşecek şekilde programlanabileceğinden bahsettik. Çünkü SQL Server, işlemlerden önce çalışması için kullanılan BEFORE Trigger özelliğini desteklemez. SQL Server’da olaydan önce devreye giren trigger’lar olarak INSTEAD OF kullanılabilir. INSTEAD OF trigger’lar, gerçek tablolar değişiklikten etkilenmeden önce devreye girerler.

Not : Oracle eğitimlerimde BEFORE Trigger’ları anlatmıştım. Bu trigger’lar bir işlem gerçekleştirilmeden önce çalışırlar. SQL Server bu sorunu INSERTED ve DELETED adında iki sözde (pseudo) tablo ile çözmüştür. Bu tabloları ilerleyen bölümlerde
inceleyeceğiz. Oracle’ın BEFORE Trigger’ı ile SQL Server’ın INSTEAD OF ve INSERTED, DELETED tablo modeli açısından sonuç anlamında pek fark yoktur. Ancak kullanılan yöntem ve mimari farklıdır.

SQL Server da trigger’lar, T-SQL ile oluşturulabildiği gibi, Management Studio ile de oluşturulabilir. SQL Server, DML (Data Manipulation Language) komutları olan INSERT, UPDATE, DELETE’e destek verdiği gibi, DDL (Data Defination
Language) komutları olan CREATE, ALTER, DROP sorguları için de trigger oluşturmayı destekler. DDL trigger’lar sadece transaction’dan sonra devreye girebilir, INSTEAD OF olamazlar.

Trıgger’lar Nasıl Çalışır?

Trigger’lar bir transaction olarak çalışırlar. Hata ile karşılaşıldığında ROLLBACK ile yapılan işlemler geri alınabilir. Trigger yapısının en önemli unsurlarından biri sözde tablolardır. Sözde tablolar, INSERTED ve DELETED olmak üzere iki adettir.
Bu tablolar RAM üzerinde mantıksal olarak bulunurlar. Gerçek veri tablosuna veri eklendiğinde, eklenen kayıt INSERTED tablosuna da eklenir. Tablodan bir kayıt silindiğinde ise silinen kayıt DELETED tablosunda yer alır. Trigger’lar güncelleme işlemi için UPDATED isimli bir sözde tabloya sahip değildir. Güncellenen veriler ilk olarak silinerek DELETE işlemi ve sonrasında tekrar
eklenerek INSERT işlemi gerçekleştirilir. Bir kayıt güncellendiğinde orijinal kayıt DELETED tablosunda, değişen kayıt ise INSERTED tablosunda saklanır.

Trigger Türleri ve INSERTED, DELETED Tabloları

Trigger’lar farklı ihtiyaçlara yönelik farklı türlere sahiptir. Bir trigger’ın, veri ekleme, güncelleme ya da silme işleminden sonra tetiklenmesi gerektiği durumlarda farklı seçenekler tanımlanması gerekir. Tanımlanan bu seçenekler ile trigger tam olarak ne yapacağını bilebilir.

Trigger’lar, veri ekleme, silme ve güncelleme işlemlerini gerçekleştirmek için INSERTED ve DELETED sözde tablolarına ihtiyaç duyar. İlerleyen anlatımlarda bu tablolara da değinerek çeşitli örnekler yapacağız.

INSERT Trigger

FOR INSERT ile kullanılır. Tabloya yeni bir veri eklemek istendiğinde tetiklenen trigger türüdür. Eklenen her yeni satır, trigger var olduğu sürece var olan INSERTED tablosuna kaydedilir. INSERTED tablosu trigger’lar ile bütünleşik bir kavramdır. Bir trigger’dan önce de yoktur, sonra da. Sadece trigger varken vardır.

DELETE Trigger

FOR DELETE ile kullanılır. Tablodan bir veri silme işlemi gerçekleştirilirken kullanılır. Silinen her kaydın bir kopyası DELETED tablosunda tutulur. Trigger ile kullanılır.

UPDATE Trigger

FOR UPDATE ile kullanılır. Tablodaki güncelleme işlemleri için tetikleme gerçekleştirmede kullanılır. Güncelleme işlemlerine özel bir UPDATED tablosu yoktur. Bunun yerine, güncellenen bir kayıt, tablodan silinmiş ve daha sonra tekrar eklenmiş olarak düşünülür. INSERT ve DELETE işlemlerini gerçekleştirdiği için doğal olarak INSERTED ve DELETED tablolarından erişilir. INSERTED ve DELETED tabloların satır sayıları eşittir.

Trigger Oluşturmak

SQL Server’da iki farklı türde trigger oluşturulabilir. FOR ve AFTER ile transaction’dan sonra tetiklenecek, INSTEAD OF ile de tablolar değişiklikten etkilenmeden önce tetiklenecek trigger’lar oluşturulabilir. Bu DML trigger’ları INSERT, UPDATE, DELETE işlemleri için oluşturulabilir.

Trigger’lar tüm veritabanı nesneleri gibi CREATE ifadesiyle oluşturulur. CREATE TRIGGER ifadesini çalıştırabilmek için şu veritabanı izinlerinden birine sahip olunmalıdır.

• sysadmin server rolü
• db_owner sistem rolü
• db_dlladmin sistem rolü

Kullanıcının sahibi olduğu view ya da tablo üzerinde herhangi bir ek izne sahip olmasına gerek yoktur.
Trigger’lar otomatik tetiklenen bir nesne olması nedeniyle içerisinde kullanılacak ifadelerde bazı kısıtlamalar olması veri güvenliği ve tutarlılığı açısından gereklidir.

Trigger’lar şu ifadeleri içeremez;

• CREATE DATABASE
• LOAD LOG
• ALTER DATABASE
• RESTORE LOG
• DROP DATABASE
• DISK INIT
• LOAD DATABASE
• DISK RESIZE
• RESTORE DATABASE
• RECONFIGURE

INSERT Trigger

Bir tabloya yeni kayıt ekledikten sonra devreye girecek işlemler için kullanılır. Trigger oluşturulduktan sonra, yeni eklenen her kaydı Inserted tablosunda tutmaya başlar. Bu kayıtlar gerçek tablonun yapısal bir kopyasıdır. SQL Server, satır bazlı trigger destek vermez. Her eklenen kayıt için değil, kayıt seti için çalışır.

Production.Location tablosuna yeni bir lokasyon bilgisi eklemek istendiğinde, ekleme işleminden sonra çalışacak basit bir trigger oluşturalım.

CREATE TRIGGER locationInsert
ON Production.Location
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
SELECT ‘Yeni lokasyon bilgisi eklendi.’;
SET NOCOUNT OFF;
END;

Oluşturulan trigger, Management Studio içerisinde, ilgili tablonun Triggers menüsünde görüntülenebilir.
Trigger’ın tetiklenmesi için bir kayıt ekleyelim.

INSERT INTO Production.Location(Name, CostRate, Availability, ModifiedDate)
VALUES(‘Yeni Lokasyon’, 0.00, 0.00, GETDATE());

INSERT sorgusu ile birlikte kayıt eklenecektir. Ekleme işleminden hemen sonra da, ekranda aşağıdaki gibi bir bilgi mesajı görüntülenecektir.

Yeni eklenen her personel için bir bildirim zorunluluğu oluşturmak istenebilir. Bunu, trigger içerisinde RAISERROR kullanarak gerçekleştirebiliriz. Bu örnekte, bir personel yerine ürün ya da sipariş ekleme gibi bir senaryoda söz konusu olabilirdi.

CREATE TRIGGER trg_PersonelHatirlatici
ON Personeller
AFTER INSERT
AS
RAISERROR (‘Eklenen Personeli Bildir’, 16, 10);

RAISERROR’un 2. parametresi olarak verilen 16 bizim hata seviyemiz. Bu değerin yüksek olması kritik bir hata olduğunu gösterir. Bu nedenle de kırmızı bir hata mesajıyla karşılaşılır. Bilgi seviyesinde bir hata mesajı verilmesini istiyorsak 10 gibi bir hata seviyesi belirleyebiliriz.

Bir çok durumda, yapılan işlemlerin anlık olarak otomatik loglama ihtiyacı duyulur. Bu ihtiyacı karşılamak için trigger kullanılabilir.

Production.Product tablosunda yapılan her veri ekleme işlemini otomatik olarak ProductLog tablosuna kaydedelim.
ProductLog tablosunu oluşturalım.

CREATE TABLE ProductLog
(
ProductID INT,
Name VARCHAR(50),
ProductNumber NVARCHAR(25),
ListPrice DATETIME
);

Production.Product tablosunda anlık loglama içerisinde, bulunmasını istediğimiz sütunları barındıracak ProductLog tablosunu oluşturduk. Bu tabloda belirtilen sütunların birebir karşılığını, INSERTED isimli sözde tablodan alacağız.

CREATE TRIGGER trg_ProductLog
ON Production.Product
AFTER INSERT
AS
BEGIN
INSERT INTO ProductLog SELECT ProductID, Name,
ProductNumber, ListPrice
FROM inserted;
END;

Oluşturulan trigger Management Studio’da, Production.Product tablosu içerisindeki Triggers bölümünde görülebilir.
Trigger’ı test etmek için veri ekleyelim.

INSERT INTO Production.Product
(Name, ProductNumber, MakeFlag, FinishedGoodsFlag, SafetyStockLevel,ReorderPoint, StandardCost, ListPrice,
DaysToManufacture, SellStartDate,rowguid, ModifiedDate)
VALUES(‘Test Ürün’,’SK-3335’, 1, 0, 500, 700, 0, 0, 3,
GETDATE(), NEWID(), GETDATE());

Normalde tek veri ekleme sorgusu çalıştığında tek bir “1 row(s) effected” mesajı döner. Ancak bu sorgu ile birlikte, iki kez aynı metnin Messages ekranında görüntülendiği görülür. Bunun nedeni; ilk metin INSERT sorgusunu belirtirken, ikinci sorgu trigger tarafından tetiklenerek, ProductLog tablosuna eklenen veri için otomatik olarak oluşturulan ve kullanılan INSERT sorgusunun mesajıdır.

SELECT ProductID, Name, ProductNumber FROM Production.Product
WHERE ProductNumber = ‘SK-3335’;

Tekrar ProductLog tablosunu listeleyelim

SELECT * FROM ProductLog;

Kaynak : Yazılımcılar İçin İleri Seviye T-SQL Programlama

Yorumla

Yorum

  1. ileri seviye t/sql kitabınızı okudum ama bir sorun var:

    kitaplar
    kitap_id primary key
    kitap adı

    yazarlar
    yazar_id primary key
    yazar adı

    kıtapyazarlar
    kitap ıd foregin key
    yazar ıd foregin key

    kitap_id ve yazar_id kısımları identity.yani otomatik.kullanıcı, değer girmiyor.
    ama hangi kitabın hangi yazara ait olduğunun belli olması için kitaplaryazarlar tablosuna id lerin otomatik girilmesi gerekli.

    hem kitap hem yazar tablosu için iki tirgger yazdım.kitap ve yazar tablosuna veri eklendğinde kıtapyazarlar tablosuna veri ekliyorlar:

    CREATE TRIGGER KYKAYIT ON KITAPLAR
    FOR INSERT
    AS
    DECLARE @KITAP_ID INT
    SelECT @KITAP_ID = KITAPLAR.KITAP_ID FROM INSERTED KITAPLAR;
    INSERT INTO KITAPLARYAZARLAR (KITAP_ID) VALUES (@KITAP_ID)
    ———————————- —————————————————————————
    CREATE TRIGGER YKKAYIT ON YAZARLAR
    FOR INSERT
    AS
    DECLARE @YAZAR_ID INT
    SELECT @YAZAR_ID=YAZARLAR.YAZAR_ID FROM INSERTED YAZARLAR;
    INSERT INTO KITAPLARYAZARLAR (YAZAR_ID) VALUES (@YAZAR_ID)

    ama şimdi de şöyle bir sorun var.iki id’yi aynı satıra eklemiyor.alt alta ekliyor.kırmızı ile işaretli olanları ben elle ekledim.

    http://i.hizliresim.com/lkNJOk.png