SQL TRIGGER KAVRAMI

Deneme adında bir veri tabanı oluşturalım ve Id, ContactId, UrunAd kolonlarına sahip Siparis adında bir tablo yaratalım.

CREATE DATABASE DENEME

CREATE TABLE Siparis

(

      SiparisId INT IDENTITY(1,1),

      ContactId INT NOT NULL,

      UrunAd NVARCHAR(20)

)

ContactId kolonunun AdventureWorks veri tabanındaki Person.Contact tablosundaki ContactId’leri temsil ettiğini düşünelim. Normalde tablolar arası ilişki kurularak bu işlem yapılabilirdi. Dolayısı ile Person.Contact tablosunda olmaya bir değer girilmesini önleyecektik. Ancak bahsi geçen tablolar farklı veri tabanlarında olduğu için ilişki kurulabilmesi söz konusu değildir.

Amacımız, girilen her siparişteki contactId bilgisinin, Person.Contact tablosunda var olup olmadığını kontrol etmek olacak. İlişki kuramadığımızdan dolayı bu işlemi trigger ile yapıyor olacağız.

CREATE TRIGGER trg_CheckContactId

ON Siparis

AFTER INSERT

AS

      DECLARE @contactId INT

      SELECT @contactId=ContactId FROM Inserted

      IF(NOT EXISTS ( SELECT * FROM AdventureWorks.Person.Contact WHEREContactID=@contactId))

            BEGIN

                  RAISERROR('Eklediğiniz ContactId mevcut değil',14,1)

                  ROLLBACK         

            END

RAISERROR ile bir hata iletisi oluşturduk. İlk parametre hata mesajı, ikinci parametre hatanın boyutu (genelde 0-19 arası veririz, 20’den büyük değerler hatanın boyutunun da büyük olduğunu gösterir), son paramtere de durum bilgisidir. Her hatayı temsilen farklı bir değer kullanmayı tercih edebiliriz.

Siparis tablosuna birkaç insert denemesi yaparak test etmeye çalışalım.

INSERT INTO Siparis VALUES(1,'Mp3 player')

INSERT INTO Siparis VALUES(2,'Mp3 player')

INSERT INTO Siparis VALUES(30000,'Mp4 player') --bu id person.contact'da olmadığından exception alırız.

İlk iki insert başarılı bir şekilde gerçekleşirken, son satırda yazmış olduğumuz trigger devreye girecek ve  hata alacağız.

Siparis tablosundaki dataları görüntülediğimizde ilk iki satırı görürken, 30000 id’li “Mp4 Player” adlı ürünü göremeyeceğiz. Şimdi de Oyuncu adlı bir tablo oluşturalım.

CREATE TABLE Oyuncu

(

      OyuncuId INT IDENTITY(1,1),

      Ad NVARCHAR(30),

      Yas INT,

      ModifiedDate DATETIME

)

ALTER TABLE Oyuncu

ADD CONSTRAINT DF_Oyuncu_ModifiedDate DEFAULT GETDATE() FOR ModifiedDate

Amacımız, tablo üzerinde bir güncelleme yapıldığında ModifiedDate kolonunu otomatik olarak update etmek olacak. Ancak yapılan güncelleme ModifiedDate kolonu üzerinde yapılıyosa bu işlem gerçekleşmemeli.

CREATE TRIGGER trg_UpdateOyuncu

ON Oyuncu

AFTER UPDATE

AS

      IF NOT UPDATE(ModifiedDate)--Eğer bu kolon update olmamışsa

      BEGIN

            UPDATE O SET ModifiedDate=GETDATE() FROM Oyuncu O

            JOIN Inserted I ON O.OyuncuId=I.OyuncuId

      END

Birkaç kayıt ekleyelim ve daha sonra update işlemi gerçekleştirelim.

INSERT INTO Oyuncu(Ad,Yas) VALUES ('Takımın dinamosu Fink',32)

INSERT INTO Oyuncu(Ad,Yas) VALUES ('Danyal',30)

INSERT INTO Oyuncu(Ad,Yas) VALUES ('Tahsin',29)

UPDATE Oyuncu SET Yas=30 WHERE OyuncuId=1

Gördüğünüz gibi kolon otomatik olarak güncellenmiş durumda. Şimdi de modifiedDate kolonu üzerinde bir değişiklik yapmayı deneyelim.

UPDATE Oyuncu SET ModifiedDate='2000-1-1' WHERE OyuncuId=1

Trigger içerisinde yaptığımız kontrol sayesinde otomatik güncelleme işlemi gerçekleştirilmedi.