Trigger oluşturma: değişiklik kaydı tutan tetikleyici yazmak

Trigger ne zaman gerekir, ne zaman gerekmez?

Trigger (tetikleyici), bir tabloya ya da görünüme yapılan INSERT, UPDATE veya DELETE işleminden sonra (ya da yerine) kendiliğinden çalışan, saklı yordama benzer bir koddur. Kavramın tanımı sözlük sayfasındadır; burada konu, bir tetikleyicinin SQL Server'da nasıl yazıldığı ve değişiklik kaydı gibi gerçek bir ihtiyaçta nasıl kullanıldığıdır.

Tetikleyiciyi çekici kılan, uygulama kodundan bağımsız çalışmasıdır: veriyi hangi uygulama, hangi kullanıcı ya da hangi betik değiştirirse değiştirsin kural işler. En tipik kullanımlar denetim kaydı (kim neyi ne zaman değiştirdi), tablolar arası kural denetimi (CHECK kısıtının yetmediği çapraz tablo kuralları) ve türetilmiş bilgiyi güncel tutmaktır.

Öte yandan tetikleyici görünmez bir mantıktır. Komutu yazan kişi onu çoğu zaman bilmez, hata ayıklarken beklenmedik yan etkiler çıkarır. Aynı iş bir kısıtla (PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT), bir saklı yordamla ya da uygulama katmanında çözülebiliyorsa o seçenekler genellikle daha okunur ve izlenebilirdir. Tetikleyici, kuralın hangi yoldan veri değiştirilirse değiştirilsin işlemesi gerektiğinde akla gelen araçtır.

CREATE TRIGGER: söz dizimi ve çalışma mantığı

SQL Server'da bir DML tetikleyicisi CREATE TRIGGER komutuyla tanımlanır. Komutta şu parçalar bulunur: tetikleyicinin adı, bağlı olduğu tablo ya da görünüm (ON), tetikleme zamanı (AFTER ya da INSTEAD OF) ve hangi işlemlerde çalışacağı (INSERT, UPDATE, DELETE, bunlardan biri ya da birkaçı). AS sözcüğünden sonra tetikleyicinin gövdesi gelir.

AFTER tetikleyicisi, tetikleyen komut başarıyla bittikten ve kısıtlar kontrol edildikten sonra çalışır. INSTEAD OF tetikleyicisi ise komutun yerine geçer; komutun kendi etkisi yapılmaz, siz ne yapılacağına karar verirsiniz. Microsoft belgelerine göre AFTER tetikleyicileri görünümlerde tanımlanamaz, INSTEAD OF ise tablo ve görünümlerde kullanılabilir.

Gövdede özel mantıksal tablolar kullanılır. inserted tablosu eklenen ya da güncellemeden sonraki yeni satırları, deleted tablosu silinen ya da güncellemeden önceki eski satırları taşır. UPDATE işlemi ikisini de doldurur: eski değerler deleted'da, yeni değerler inserted'dadır. Bu tablolar yalnızca tetikleyicinin içinde vardır ve tetikleyen komutun etkilediği satırları içerir.

Örnek: stok değişikliklerini denetim tablosuna yazmak

Bir depo uygulamasında ürün stoğunun kim tarafından ne zaman değiştirildiğini kaydetmek istiyorsunuz. Önce kaydın tutulacağı tabloyu oluşturursunuz:

CREATE TABLE dbo.UrunStokLog ( log_id INT IDENTITY PRIMARY KEY, urun_id INT NOT NULL, eski_stok INT NULL, yeni_stok INT NULL, degisiklik_zamani DATETIME NOT NULL DEFAULT GETDATE(), kullanici NVARCHAR(MAX) NOT NULL DEFAULT SYSTEM_USER );

Ardından Urun tablosundaki stok sütunu değiştiğinde çalışacak tetikleyiciyi yazarsınız:

CREATE TRIGGER dbo.trg_Urun_StokDegisti ON dbo.Urun AFTER UPDATE AS BEGIN SET NOCOUNT ON; IF NOT EXISTS (SELECT * FROM inserted) RETURN; IF NOT UPDATE(stok) RETURN;

INSERT INTO dbo.UrunStokLog (urun_id, eski_stok, yeni_stok) SELECT i.urun_id, d.stok, i.stok FROM inserted AS i JOIN deleted AS d ON d.urun_id = i.urun_id WHERE i.stok <> d.stok OR (i.stok IS NULL AND d.stok IS NOT NULL) OR (i.stok IS NOT NULL AND d.stok IS NULL); END;

Satırların anlamı şöyledir. SET NOCOUNT ON, tetikleyicinin istemciye fazladan sayı mesajı göndermesini önler. Boş kümeyi erken bırakan ilk RETURN, hiçbir satır değişmediğinde gereksiz işlem yapmamayı sağlar; Microsoft belgeleri bu amaçla ROWCOUNT_BIG kontrolünü önerir. UPDATE(stok) denetimi, güncellemede stok sütununa dokunulmuşsa çalışır. Son INSERT, inserted ile deleted'ı urun_id üzerinden birleştirip yalnızca değeri gerçekten değişen satırların eski ve yeni halini kaydeder.

Denemek için birkaç ürünün stoğunu tek UPDATE ile değiştirin ve log tablosunda her ürün için ayrı bir satır oluştuğunu doğrulayın.

Çok satırlı işlemler: tetikleyici satır başına değil komut başına çalışır

SQL Server'da DML tetikleyicisi, komut bir satırı da etkilese yüzlerce satırı da etkilese, komut başına bir kez çalışır. Bu en sık yapılan hatanın kaynağıdır: tetikleyiciyi tek satır değişiyormuş gibi yazmak. Test sırasında tek satırlık güncellemelerde sorunsuz çalışan bir tetikleyici, üretimde toplu bir UPDATE geldiğinde yanlış satırı kaydeder ya da hata verir.

Tipik bir tuzak, inserted tablosundan bir değişkene değer atamaktır: SELECT @yeni = stok FROM inserted. Birden çok satır varsa değişkene yalnızca biri düşer ve kalanlar sessizce kaybolur. Çözüm, tetikleyiciyi baştan sona küme tabanlı yazmaktır: inserted ve deleted tablolarını JOIN ile birleştirip tek bir INSERT ... SELECT ya da UPDATE ... FROM ile işlemek.

Başka bir sık hata, tetikleyiciden istemciye sonuç kümesi döndürmektir. Belgelere göre tetikleyicilerden sonuç kümesi döndürme yeteneği gelecek sürümlerde kaldırılacaktır; bu tür sonuçlar sonuç beklemeyen uygulamalarda beklenmedik davranışa yol açar. Tetikleyicide sonuç döndüren SELECT yazmayın, değişken atama gerekiyorsa gövdenin başına SET NOCOUNT ON koyun.

AFTER mı INSTEAD OF mu? DELETE ve TRUNCATE'te ne olur?

Çoğu denetim ve kayıt işinde AFTER tetikleyicisi yeterlidir: komut başarılı olduktan sonra kaydı yazarsınız. Komutun kendisini değiştirmek ya da engellemek istiyorsanız (örneğin yalnızca belirli bir kullanıcının silmesine izin vermek, silme yerine "pasif" işaretlemek) INSTEAD OF devreye girer. Görünümler üzerinde ekleme ve güncelleme yapılabilmesi için de çoğu zaman INSTEAD OF tetikleyicisi gerekir.

Belgelerdeki bazı kısıtlar bilmeye değer: bir tablo ya da görünümde her işlem için en çok tek bir INSTEAD OF tetikleyicisi tanımlanabilir. ON DELETE ya da ON UPDATE için basamaklı (cascade) yabancı anahtarı olan tablolarda INSTEAD OF DELETE ve INSTEAD OF UPDATE tetikleyicisi tanımlanamaz.

Silme izleme konusunda önemli bir uyarı: TRUNCATE TABLE tek tek satır silmeleri kaydetmediği için DELETE tetikleyicisini çalıştırmaz. Silmeleri denetim amacıyla izliyorsanız, TRUNCATE yetkisi olan kullanıcıların tetikleyiciyi atlayabileceğini hesaba katın.

Tetikleyicinin içinde işlemi geri alabilirsiniz; belgelerdeki bir örnek, bir kural ihlalinde RAISERROR ile hata verip ROLLBACK yaparak ekleme işlemini iptal eder. Bu güçlü bir araçtır ama kullanıcıya anlamlı bir hata mesajı vermeyi ihmal etmeyin.

Riskler ve sık yapılan hatalar

Tetikleyiciler sorun çıkardığında genellikle şu nedenlerle çıkar: • Uzun süren gövde: tetikleyici, tetikleyen komutla aynı işlemin içinde çalışır ve bitene kadar kaynakları kilitli tutar. Belgeler tetikleyicileri mümkün olduğunca kısa tutmayı önerir. • Zincirleme tetiklenme: bir tetikleyici başka bir tabloyu değiştirir, o tablonun tetikleyicisi çalışır ve bu böyle sürebilir. Sonsuz döngü riski vardır; sistem iç içe çağrılara bir sınır koyar. • Sırası belli olmayan çoklu tetikleyici: aynı olay için birden çok tetikleyici varsa, ilk ve son AFTER tetikleyicisi dışında çalışma sırası tanımsızdır. Her tetikleyici kendi başına yeterli olmalıdır. • Gizli iş mantığı: kuralı yalnızca tetikleyicide tutmak, yeni geliştirici ya da veri taşıma işi için sürpriz demektir. Tetikleyicileri belgeleyin. • Veri yükleme sırasında beklenmedik yavaşlama: toplu yüklemede her komutta tetikleyici çalışır; yükleme öncesi bu durumun bilinmesi gerekir.

Bir tetikleyiciyi yayına almadan önce şu senaryoları deneyin: tek satır, çok satır, hiç satır etkilemeyen komut, hata durumunda geri alma ve toplu yükleme. Sonra mevcut tetikleyicileri bir sorgu ile listeleyip hangi tabloda hangi mantığın çalıştığını görünür kılın.

Nereden başlanır, Mindset programlarında nerede çalışılır?

Tetikleyici yazmadan önce INSERT, UPDATE, DELETE komutlarını, JOIN'i ve transaction mantığını rahat kullanıyor olmak gerekir; tetikleyici bu bilgilerin üzerine kurulur. Önce küçük bir deneme veritabanında denetim tablosu örneğini kendiniz yazmak, bu beceriyi oturtmanın en pratik yoludur.

Mindset kataloğunda SQL ile Programlama Eğitimi, Tablolarla Çalışma, Veri İşleme Dili ve Görüntüleme modül başlıklarını içerir. Tetikleyici modül başlıklarında ayrıca anılmaz; bu modüller, tetikleyicinin dayandığı tablo, satır işleme ve nesne tanımı temelini verir. Veritabanında kayıt ve kural yönetimiyle uğraşan analist, geliştirici ve operasyon ekipleri için uygun bir zemindir.