Trigger_5
10 Ocak 2017 Salı
DDL Trigger'ları, herhangi bir DDL(CREATE, ALTER, DROP) *olayından sonra devreye giren yapılardır. Bu *olaylar, bir tablo, index, stored prosedür ya da fonksiyon üzerinde gerçekleşen herhangi bir DDL işlemi olabilir. DDL trigger'ların kullanım alanı 2 farklı uzayda gerçekleşir. Bunlar; veritabanı seviyesi ve sunucu seviyesi olmak üzere 2'ye ayrılır. (Hatırlarsanız klasik bir DML trigger sadece tablo uzayında tanımlanabiliyor idi.) DDL trigger'lar bir tablonun yaratılması(CREATE_TABLE), bir view'in silinmesi(DROP_VIEW) gibi *olaylar için tanımlanabileceği gibi veritabanındaki tüm DDL işlemlerine tepki vermek(DDL_DATABASE_LEVEL_EVENTS) amacıyla da programlanabilir. Sunucu seviyesinde tanımlanan bir DDL trigger ise tüm veritabanlarındaki olaylara tepki vermek amacıyla kullanılabilir. Bir DDL trigger'ın yazım şekli(sentaks)'ne bakalım dilerseniz:
CREATE TRIGGER Trigger_Adı
ON [DATABASE | ALL SERVER]
FOR DatabaseOlayTürü | ServerOlayTürü
AS
BEGIN
--Trigger mantıksal kodları
END
Not: DDL trigger'lar INSTEAD OF olamazlar. Her zaman transaction'dan sonra devreye girecek şekilde programlanırlar. (burada kastetmek istediğim, DDL trigger'ların her zaman AFTER trigger olarak programlanabileceğidir.)
DDL trigger'lar çeşitli amaçlar için kullanılabilirler. Örneğin, bir DDL trigger kod gövdesinde yapacağınız uyarı ile değişikliklerden haberdar olabilir(log) ya da bu değişikliklerin olmasını önleyebilirsiniz. Yani bir tablo üzerinde muhtemel olabilecek tüm DDL (CREATE, ALTER, DROP) operasyonların önüne geçebilirsiniz. Ya da veritabanı üzerinde bir nesne için yapılan değişikliğin, kullanıcı bilgisi, üzerinde değişiklik yapılan nesnenin adı (nesneyi kapsayan uzay dahil) ve o değişikliğe sebep olan kodları, zaman bilgisi dahil yakalamanız (bir tabloya yazdırmak) mümkün.
Önemli Not: DDL trigger'lar, DML trigger'lar gibi INSERTED ve DELETED sözde tablolarına erişemezler. DDL trigger'lar ortam hakkındaki bilgiyi eventdata() fonksiyonu ile alabilirler. (Aslında bunun sebebi DDL trigger'ların veri ile ilgili işlemlere bakmadıklarından kaynaklandığını söyleyebilirim.)
Database seviyesinde örnek bir trigger oluşturalım:
CREATE TRIGGER trg_CreateInfo_DDLTrigger
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
Print 'Yeni bir tablo oluşturuldu.'
END
Bu trigger'ın yaratıldığı database üzerinde CREATE TABLE Test(Id int) dediğimde ilgili trigger'ım tetiklenecek ve kod gövdesinde tanımladığım aksiyonu verecektir.
Bir trigger içersinde birden fazla olay(event) tanımlaması yapılabilir. Tıpkı bir alt satırda bulunan örneğimizde olduğu gibi.
ALTER TRIGGER trg_CreateInfo_DDLTrigger
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
Print 'Bir tablo oluşturdunuz, değişiklik yaptınız ya da sildiniz.'
END
Bu trigger ile gördüğünüz gibi birden fazla olayı (CREATE, ALTER, DROP gibi) yakalamamız mümkün.
DDL trigger'lar oluşturulduktan sonra (DatabaseAdı>Programmability>DatabaseTriggers başlıkları altında) database'de bir nesne olarak yerlerini alırlar. Örnek Resim-1'de DDL trigger'ların barınma yeri, örnek olarak verilmiştir.
Bakınız Resim-1: 2 adet canlı örnekte database'de oluşturulan nesneler için kullanıcı bilgilendirmesi yaptık. Bu nesnelerin oluşturulmasını engelleyici trigger'a bakalım birde:
ALTER TRIGGER trg_CreateInfo_DDLTrigger
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
ROLLBACK
Print 'Nesne yaratma, değiştirme yada silme yetkiniz yok !'
END
Trigger'la engellenen nesne değişikliğinin önüne 2 türlü geçilebilir. İlgili trigger'ı tamamen silmek ya da geçici süre etkisiz kılmak bu yöntemleri kapsar. Enable/disable sözcükleriyle geçici süre etkisiz kılma işlemlerini kolayca yapabilirsiniz. Örnek kullanım şekli aşağıdaki gibidir:
ENABLE TRIGGER trg_CreateInfo_DDLTrigger ON DATABASE
DISABLE TRIGGER trg_CreateInfo_DDLTrigger ON DATABASE
DROP TRIGGER trg_CreateInfo_DDLTrigger ON DATABASE
ifadesi ile de ilgili nesneyi veritabanından tamamen kaldırabilirsiniz.
Sistem stored prosedürü olarak bildiğimiz sp_rename sözcüğü trigger içersinde RENAME olay(event) adı ile kullanılabilir. Öncelikle sp_rename SP'sinin kullanımını hatırlayalım:
Tablo adının değişimi için yazım şekli(sentaks):
sp_rename 'MevcutTabloAdı', 'YeniTabloAdı'
Sütun adının değişimi için yazım şekli(sentaks): (Örneğin tblUrun tablosundaki UrunID sütununun adını ID olarak değiştiğimizi varsayalım)
sp_rename 'tblUrun.UrunID', 'ID', 'column'
Şimdi RENAME olayının trigger üzerindeki yansımasının yazım şekline bakalım:
CREATE TRIGGER trg_ForRenameTrigger
ON DATABASE
FOR RENAME
AS
BEGIN
Print 'Nesne isminde değişiklik yapıldı.'
END
Şayet istersek Begin sözcüğünden sonra ROLLBACK kullanarak, nesne ismi değişikliğinin önüne geçebiliriz ya da insert into deyimi ile nesne değişikliği halinde bunu bir tabloya kaydederek log günlüğü de yaratılabilir.
Not: Üzerinde çalıştığınız veritabanındaki, tüm veritabanı seviye trigger'ların listesini görmek için:
SELECT * FROM sys.triggers
WHERE parent_id=0
sorgusunu kullanabilirsiniz. Şayet where bloğunu kaldırırsanız DML türdeki trigger'larıda görebilirsiniz.
SUNUCU SEVİYE TRIGGER'LAR
Veritabanı seviye trigger'lardan farklı olarak sunucunun tümünde meydana gelebilecek olaylara karşı tepkime verirler. Yeni bir veritabanı oluşturulması ya da yeni bir rol eklenmesi gibi işlemler bu gruptandır. Etki alanını belirtmek için ALL SERVER sözcüğünü kullanmalısınız. Sunucu seviye trigger'ları görüntüleyebilmek için şu ifadeyi yazıyor olmanız gerekir:
SELECT * FROM master.sys.server_triggers
Bu katalog yardımıyla tüm trigger'ları görebilirsiniz.
Örneğin veritabanı sunucusundaki login oluşturma, silme ve değiştirme olaylarına karşı bir trigger oluşturmak isterseniz, tanımlanacak olay adı, FOR DDL_LOGIN_EVENTS olmalıdır.
Mesela şu şekilde oluşturulan bir kullanıcı tanımlama operasyonunu rahatlıkla yakalayabilirsiniz.
CREATE LOGIN yazilimciningunlugu WITH PASSWORD='abc'
Not: Veritabanı seviyesindeki bir DDL trigger'ı silmek için,
DROP TRIGGER trigger_adı ON DATABASE
Sunucu seviyesindeki bir DDL trigger'ı silmek için ise,
DROP TRIGGER trigger_adı ON ALL SERVER
ifadelerini kullanmalıyız.
Önemli Not: Bir tablo üstünde sunucu seviyeli veya veritabanı seviyeli bir trigger tarafından erişim bağımlılığı varsa, bu trigger silinmediği sürece, bağımlı tablolar da silinemez.
Not: Sunucu seviyesindeki DDL trigger'ların depolandığı yer, Server Objects> Triggers başlığı altındadır.
TRIGGER'LARIN ÇALIŞTIRILMA SIRASI
Server seviyesindeki trigger'lar, database seviyesindeki herhangi bir trigger'dan daima önce çalışırlar. 'sp_settriggerorder' sistem SP'sini kullanarak trigger'ların çalışma sırasını değiştirebilirsiniz.
Bununla alakalı örnek bir uygulama yapalım şimdi :
CREATE TRIGGER trg_DatabaseScope
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
Print 'Database Scope Trigger'
END
CREATE TRIGGER trg_ServerScope
ON ALL SERVER
FOR CREATE_TABLE
AS
BEGIN
Print 'Server Scope Trigger'
END
Biri server seviye diğeri database seviyesinde olmak üzere 2 adet trigger tanımlıyorum. Sonrasında aşağıdaki gibi bir tablo oluşturuyorum.
create table Test(id int)
Bu çalıştırmanın akabinde tetiklenen ilk trigger server seviyesindeki trigger olacaktır. Çünkü öncelik her zaman başta da dediğimiz gibi server seviyesindeki trigger'lardadır. Şayet aynı seviyede trigger'lar tanımlarsanız bunların tetiklenme sırası oluşturulma sırasını baz alarak çalışır. Bu sıralamaya kullanıcı elle müdahale etmek isterse yukarda da belirttiğim gibi sp_settriggerorder SP'sini kullanması gerekecektir. Bu SP'nin parametrelerine bakacak olursak:
exec sp_settriggerorder
@triggername='trigger adı'
@order='First, Last yada None sözcüklerinden biri'
@stmttype='DML adı yada DDL event adı buraya yazılır.Örn:INSERT,UPDATE,CREATE_TABLE gibi'
@namespace='Trigger etki alanı. Örn:DATABASE, SERVER yada NULL gibi'