MS SQL 'de DDL Triggers

Merhaba arkadaşlar.
Şimdiye kadar gördüğümüz "DML Triggers" 'larda bir tablo üzerindeki manipülasyonları inceledik. Bir tabloya bağımlı çalışıyorlardır. Şimdi göreceğimiz "triggers" arkadaşlar ya bir veritabanı üzerindeki (create, alter, drop) yada tüm sunucudaki (all server) manipülasyonları gözlemleyerek tetiklenecek "triggers" nesneleridir. İşte bu nesnelere de "DDL Triggers" diyoruz. Örneğin veri tabanı nesneleri üzerindeki işlem yani "create, alter, drop" komutları ile manipüle edilen nesneler için "trigger" lar oluşturacağız. Bu veri tabanı nesnelerim "view, stored procedure, function, table vs.." olabilir. "DDL Triggers" nesnelerinde "After-Instead Of" gibi bir ayrım yoktur. Sadece tanımlamalarında "after" trigger söz konusudur. Fakar eğer "Instead Of  Triggers" gibi kullanmak için, tetiklenme olayının yerine bir olay gerçekleştirmek istiyorsam, trigger nesnemin gövdesi "rollback" anahtar sözcüğünü yazarak işlemin geri alınmasını sağlarım.

• Bir veritabanı üzerinde çalışan "Trigger" nesneleri

"Trigger" nesnemiz "tirgger" 'ımızı hangi veri tabanı üzerinde oluşturmuşsak sadece o veri tabanı üzerinde çalışacaktır. Kolay bir örnekle başlayalım. Örneğin "Nortwind" veri tabanında herhangi bir "view" nesnesinin silinmesi ile tetiklenen bir "trigger" yazalım. Bir "view" silindikten sonra sadece ekrana bir yazı yazsın. Bunun için "AFTER Drop_View" kullanmalıyım.

USE Northwind

CREATE TRIGGER ViewOlusturuldu

ON DATABASE

AFTER Drop_View

AS

      PRINT 'View silmede ustasin'

"Trigger" nesnemi oluşturdum. Şimdi çalışıyor mu diye bakalım. Bir veri tabanımızdan bir "view" silelim.

DROP VIEW dbo.[Summary of Sales by Year]

"View" nesnemiz başarılı bir şekilde silindi ve görünmesini istediğimiz mesajımızı aldık. Mesaj versin fakat silmesin isteseydim "trigger" nesnemi tanımlarken sonuna "rollback" sözcüğünü eklemeliydim. Böylece yapılan işlemi geri alacaktı. Şimdi başka bir örnekte de "rollback" kullanalım. Örneğin "Nortwind" veri tabanında bir tabloda herhangi bir değişiklik işlemi yapılamasın. 

USE Northwind

CREATE TRIGGER TabloDuzenlemeEngeli

ON DATABASE

FOR Alter_Table

AS

      RAISERROR('Tablo düzenleme engeli',16,1)

      ROLLBACK

NOT: "On Database" diyerek oluşturduğumuz "DDL Trigger" nesneleri hangi veri tabanında oluşturulmuşsa, "Object Explorer" penceresinde o veri tabanı altında "Programmability", "Database Triggers" sekmesi altında yerini alır.

"Trigger" nesnemi "hata mesajımla birlikte" oluşturdum. Hata mesajına verdiğim parametrelerin anlamına, kursörunuzu "Raiserror" komtunun üstüne getirdiğinizde "shift+F1" yaparak bakabilirsiniz. 2. parametresi hatanın önem derecesidir, 10 'dan büyükse "Catch" bloğuna düşer. 20 'den büyükse(25 ' kadar) "fatal error" olur. 3. parametresi ise hatanın durumudur. Yapılan işlemi de "rollback" ile geri aldım. Şimdi çalışıyor mu diye bakalım. "Shippers" tablosuna "Hede" adında bir kolon eklemeye çalışalım. 

ALTER TABLE Shippers ADD Hede NVARCHAR(24) NULL

Hata mesajından da anlaşılacağı üzere kolonumu tabloma eklemeye izin vermedi, ekleyemedim, "transaction" sonladı, işlem iptal oldu. İşlemimiz "TabloDuzenlemeEngeli" adındaki "procedure" nesnesine takıldı. "Trighers" nesneleri otomatik çalışan "Stored Procedure" 'lerdir demiştik. Görüldüğü üzere tabirimiz doğrulandı.

Bu örnek de "DDL Triggers" 'lardaki "Instead Of" triggerı gibi tetiklenen işlem yerine, başka bir işlem yapılmasına bir örnekti arkadaşlar. Daha sonra "trigger" nesnemi "disable" ediyorum.

DISABLE TRIGGER TabloDuzenlemeEngeli ON DATABASE

• Tüm Server üzerinde çalışan "Trigger" nesneleri

Bu "trigger" nesnelerimi tanımlarken hangi ver tabanı üzerinde tanımladığımızın bir önemi yok arkadaşlar. Çünkü "All Server" yani tüm sunucu üzerinde çalışacaktır. Örneğin sunucumda hiç bir şekilde veri tabanı oluşturulamasın istiyorum.

CREATE TRIGGER VeriTabaniOlusturmaEngeli

ON ALL SERVER

FOR create_database

AS

      RAISERROR('Yetkilen de gel !!',16,1)

      ROLLBACK

Görüldüğü üzere arkadaşlar "All Server" deyimi ile oluşturulan "Trigger" nesneleri, "Object Explorer" penceresinde, hiç bir veri tabanı altında değil de direk sunucuda "Server Object", "Triggers" sekmesinin altında yerini alıyor. 

 "Trigger" nesnemi "hata mesajımla birlikte" oluşturdum. Bir veri tabanı oluşturmaya çalışalım bakalım oluşturabilecek miyiz.

CREATE DATABASE DenemeDB

Görüldüğü üzere işlemimiz "VeriTabaniOlusturmaEngeli" adındaki "procedure" nesnesine takıldı. Unutmadan "trigger" 'ımı "disable" ediyorum.

disable trigger VeriTabaniOlusturmaEngeli on all server

Başka bir örnekte ise işlem yapıldıktan sonra bir mesaj verelim. Sunucuda herhangi bir veri tabanından bir tablo silindiğinde mesaj versin. 

CREATE TRIGGER TabloSilme

ON ALL SERVER

AFTER DROP_TABLE

AS

      PRINT 'Tablo Silmene Hastayim'

"Trigger" nesnemizin çalışmasını kontrol etmek için herhangi ver tabanından, herhangi bir tabloyu silelim.

DROP TABLE Kitapci.dbo.Hede

Umarım yardımcı olabilmişimdir. Bir başka yazıda görüşmek dileğiyle, hoşçakalın...

MS SQL 'de DML Triggers - Instead Of Triggers

Merhaba arkadaşlar.

"After trigger" nesneleri belirtilen işlemden sonra çalışırlardı. Yani belirtilen işlem gerçekleştikten sonra çalışırlardır. Fakat "Instead Of Trigger" nesneleri ise belirtilen işlem komutu gelince tetiklenirler ve o belirtilen işlemi yapmazlar. Onun yerine bizim belirttiğimiz işlemi yaparlar.

Hemen bir örnek ile kullanımını görelim.  Bu örnek için Northwind veri tabanında çalışıyorsanız eğer, "LogFile" adında bir tablo oluşturun. "Id", "SilenKisi" ve "Tarih" adında uc kolonu olması yeterlidir. Daha sonra "UrunKoruma" adında, "Products" tablosunun üzerinde bir "trigger" oluşturalım. Örneğin bir şirkette çalışıyorsunuz ve bir tablodan bir veri silinmeye çalışıldığında; sizden verinin silinmemesi, veriyi silmeye çalışan kişinin "Computer Name" 'i istendi. Tek yapmanız gereken aşağıdaki "trigger" nesnesini oluşturmak.

CREATE TRIGGER UrunKoruma

ON dbo.Products

INSTEAD OF DELETE

AS

      INSERT INTO dbo.LogFile (SilenKisi,Tarih)

      VALUES (SUSER_SNAME() , -- SilenKisi - nvarchar(50)

      GETDATE()               -- Tarih - datetime

      )

      PRINT 'Kac oglum Patron gelio'

"LogFile" tablomuza daha hiç veri girişi yapılmamış. Şimdi oluşturduğumuz "trigger" nesnesini tetiklemeye çalışalım.

DELETE FROM dbo.Products WHERE ProductID=65

Silmeye çalıştım arkadaşlar, bakalım silmiş mi?

SELECT ProductID,ProductName FROM dbo.Products WHERE ProductID=65

Hayır arkadaşlar görüldüğü üzere silmemiş. Birinci beklentimiz de buydu zaten. İkinci beklentimiz de "LogFile" tablosuna kaydetmesiydi, bakalım bir kayıt eklenmiş mi?

SELECT * FROM dbo.LogFile

"Trigger" nesnemiz on numara çalışıyor arkadaşlar."LogFile" tabloma da beklediğim kayıt başarılı bir şekilde gelmiş.

Bir örnek daha yapalım arkadaşlar. "Hesap" adında bir tablo oluşturalım. "Id", "Ad", "Bakiye"(money) ve "SilindiMi"(bit) kolonları olsun. Tablomuza iki kayıt girelim.

Bu tablomdan bir kaydın silinmesi istenirse, silinmesinde "SilindiMi" kolonu "true" yapılsın. Zaten arkadaşlar hiçbir şirkette kolay kolay hiçbir veri silinmez. Ya başka tabloya taşınır, yada bizim yaptığımız gibi bir işlemle silindi olarak işaretlenir. Şimdi gelelim "SilindiIsaretle" adında bir "trigger" yazmaya:

CREATE TRIGGER SilindiIsaretle

ON dbo.Hesap

INSTEAD OF DELETE 

AS

      DECLARE @Id INT

      SELECT @Id= Id FROM DELETED

      UPDATE dbo.Hesap SET SilindiMi=1 WHERE Id=@Id

      PRINT 'Silmeye kiayamadim :D'

"Trigger" nesnemi tetiklemek için tablodan bir kayıt silmeye çalışalım.

DELETE FROM dbo.Hesap WHERE Id=1

Tablomuza bakalım silinmiş mi yada ne gibi bir değişiklik olmuş?

SELECT * FROM dbo.Hesap  

Amacımıza ulaştık arkadaşlar.

Başka bir yazıda görüşmek dileğiyle, hoşçakalın...

MS SQL 'de DML Triggers Giriş - After (For) Triggers

Merhaba arkadaşlar.
"Strored Procedure" nesnelerim benim için başlı başına bir iş yapabiliyordu. Tanımladıktan sonra kullanmak istediğim yerde çağırıp işimi görebiliyordum. İşte arkadaşlar "Trigger" nesnelerini de tanımlandıktan sonra otomatik çalışan "Strored Procedure" olarak tanımlayabiliriz. Otomatik olarak çalışmaktan kasıt: "Trigger" nesnelerini çalışmasını istediğim zaman çağırmaya gerek yoktur. Belirttiğim olay gerçekleştiğinde, belirttiğim işlemin yapılması söz konusudur.

"Trigger" 'lar birer veri tabanı nesneleridirler. Bu yüzden manipüle edilirken "DDL" (create, alter, drop) dili ile yönetilirler. "Trigger" nesneleri ikiye ayrılırlar: "DML Triggers" ve "DDL Triggers" olmak üzere. "DDL Triggers" nesneleri içerisinde tanımladığımız kodlar, bir veri tabanı nesnesi "create, alter, drop" işlemlerinden istediğimiz işleme uğradığında devreye girer. Bu yazımda da "DML Triggers" nesnelerini inceleyeceğiz.

"DML Triggers" nesneleri oluşturulurken bir tablo üzerinde tanımlanırlar ve bu tabloya yapılan "insert, update, delete" gibi işlemleri gözlerler. Bu davranışları itibariye "c sharp" tarafındaki "event" nesnelerine benzerler. İstediğimiz tablo üzerinde, hangi olaydan sonra veye hangi olay yerine, ne yapılmasını istiyorsak "DML Triggers" nesnelerinin gövdesinde tanımlarız. Tablo bağımlı çalıştığından dolayı, hangi tablo üzerinde tanımlanmışsa "Object Explorer" penceresinde, o tablonun altında yerini alır ve simgesi "şimşek" 'tir.

"DML Triggers" nesneleri de kendi aralarında ikiye ayrılır: "After" (For) ve "Instead Of" olmak üzere. "After" trigger 'lar kendisinden sonra belirttiğimiz işlemden sonra, bizim tanımladığımız işlemleri yapar. "Instead Of" trigger 'lar ise kendisinden sonra belirttiğimiz işlemden yerine, bizim tanımladığımız işlemleri yapar. Öncelikle "After Trigger" nesnelerinin kullanımlarını inceleyelim.

Örneğin "Northwind" veri tabanında bir ürünün siparişi verildiğinde, sipariş edilen miktar kadar o ürünün stoğundan düşsün istiorum. Bu örnekte dikkat edilmesi gereken nokta, bir üründen sipariş verilmesi demek "Order Details" tablosuna kayıt girilmesi demektir. Bu yüzden "Trigger" nesnemi "Order Details" tablosu üzerinde tanımlamalıyım. Yani "trigger" 'ım bu tabloyu izleyecek. İzleyecek ama bu tabloya "insert" işlemi yapılması olayını izleyecek. Bu yüzden tanımlarken "After Insert" diye tanımlamalıyım. Daha sonrası bildiğimiz t-sql dili, "Products" tablosunun "UnitsInStock" kolonundan sipariş edilen miktar kadar düşüreceğiz. Fakat arkadaşlar, "Order Details" tablosuna "insert" edilen son kaydın "Quantity" (miktar) değerini bilmem gerekir ki "Products" tablosunun "UnitsInStock" kolonundan bu değeri düşebileyim. Bunun için "Inserted" tablosundan yararlanacağım. Peki nedir bu "Inserted" tablosu? T-sql server dilinde "Inserted" ve "Deleted" tabloları vardır. Bu tabloları son eklenen veya son silinen kaydı tutan, tek satırlık tablolar olarak dişinebiliriz. Hangi tabloya eklendiyse veya hangi tablodan silindiyse o tablonun kolonları mevcuttur.

NOT: "Updated" tablosu yoktur arkadaşlar. Çünkü "update" işlemi, "delete" ve "insert" işlemleri olmak üzere iki aşamadan oluşur. Bir "update" işlemi gerçekleştiğinde ilk önce "delete" daha sonra arkasından "insert" işlemi gerçekleşir. Sonuç olarak "updated" tablosu yerine "deleted" ve "inserted" tabloları kullanılır.

Şimdi "trigger" nesnemizi oluşturalım.

ALTER TRIGGER StokGuncelle

ON dbo.[Order Details]

AFTER INSERT

AS 

      UPDATE dbo.Products

      SET UnitsInStock -= (SELECT Quantity FROM INSERTED)

      WHERE ProductID = (SELECT ProductId FROM INSERTED)

"StokGuncelle" adında "trigger" nesnem "Order Details" tablosu üzerinde oluştu. "Object Explorer" penceresinde aşağıda görüldüğü üzere, "Order Details" tablosu altında "Triggers" sekmesi altında, şimşek ikonuyla birlikte oluşturduk. 

Arkadaşlar eğer "trigger" nesnemin üzerinde "kırmızı ok" işareti yoksa "enable" durumdadır. Şimdi sıra geldi "trigger" nesnemi tetiklemeye.Dikkat ederseniz çağırmıyorum arkadaşlar tetikliyorum. Bir sipariş verelim bakalım. Ama öncesinde oluşturduğumuz "trigger" nesnesini tetiklemeden önce "Products" tablosundaki "ProductsId" değeri 1 olan ürünün stok değerine bir bakalım.

SELECT UnitsInStock FROM dbo.Products WHERE ProductID=1

Oluşturduğumuz "Trigger" nesnemizin doğru çalışıp çalışmadığını anlamak açısından, "trigger" 'ımızı tetikleyip aynı ürünün stoğuna tekrar bakalım. 1 nolu üründen 9 adet sipariş verelim. Eklenecek kaydı "Order Details" tablomda çok aramamak, tablomun en başında gözükmesi için "OrderId" 10248 verelim.

INSERT INTO dbo.[Order Details]

(OrderID ,ProductID ,UnitPrice ,Quantity ,Discount)

VALUES(10248, 1, 18, 9, 0)

"Insert" işleminden sonra bir adet "OrderDetails" tablosundan, bir adet de "Products" tablosundan olmak üzere iki satır etkilendi.

Emin olmak açısında sipariş verilen ürünümün stoğuna tekrar bakıyorum.

SELECT UnitsInStock FROM dbo.Products WHERE ProductID=1

Beklediğimiz gibi, görüldüğü üzere sipariş verilen ürünün stoğundan, sipariş miktarı kadar düşüldü. Yani "trigger" nesnemiz sorunsuz bir şekilde çalışıyor arkadaşlar.

Şimdi bir çok durum için bir "trigger" tanımlayalım. "Employees" tablosu üzerinde "insert, update ve delete" işlemlerinden sonra çalışsın. "After" yerine "for" yazsak da olur demiştik. Onu da yapalım. "Trigger" nesnemiz 

"Employees" tablosunda herhangi bir manipülasyon işleminde ekrana manipüle edilen kayıtları işlemiyle birlikte yazacaktır.

CREATE TRIGGER CalisanGuncelleme

ON dbo.Employees

FOR INSERT,UPDATE,DELETE

AS

      DECLARE @eskiisim NVARCHAR(20),@eskisoyisim NVARCHAR(20)

      DECLARE @yeniisim NVARCHAR(20),@yenisoyisim NVARCHAR(20),@eid INT

      IF(EXISTS(SELECT * FROM DELETED) AND EXISTS(SELECT * FROM INSERTED))

      BEGIN

            SELECT @eid=EmployeeID,@eskiisim=FirstName,@eskisoyisim=LastName 

FROM DELETED

            SELECT @yeniisim=FirstName,@yenisoyisim=LastName FROM INSERTED

            UPDATE dbo.Employees SET FirstName=@isim,LastName=@soyisim 

WHERE EmployeeID=@eid

            PRINT2 @eskiisim+ ' ' + @eskisoyisim + ' isimli calisan '+@eskiisim+' '+@eskisoyisim+' olarak guncellendi...'

      END

      ELSE IF(EXISTS(SELECT * FROM INSERTED))

      BEGIN

            SELECT @yeniisim=FirstName,@yenisoyisim=LastName FROM INSERTED

            PRINT @yeniisim+ ' ' + @yenisoyisim + ' isimli calisan eklendi...'

      END

      ELSE IF(EXISTS(SELECT * FROM DELETED))

      BEGIN

            SELECT @eskiisim=FirstName,@eskisoyisim=LastName FROM DELETED

            PRINT @eskiisim+ ' ' + @eskisoyisim + ' isimli calisan silindi...'

      END

NOT: MS Sql Server 'da iç içe 32 tane "trigger" nesnesi çalıştırılabilir.

Trigger Nesnenin Çalışmasını Durdurmak

Eğer "enable" durumdaki bir "trigger" nesnenizin çalışmasını istemiyorsanız ya disayndan yani "Object Explorer" penceresinden çalışmasının durmasını istediğimiz "trigger" nesneme sağ tık "disable" yapabilirim. Yada aşağıdaki kod satırı ile, "trigger" nesnem hangi database nesnesi üzerinde çalıştığını belirterek, "trigger" nesnemi "disable" edebilirim.

Başka bir yazıda görüşmek dileğiyle, hoşçakalın...