21 Nisan 2011 Perşembe

SQL Trigger Kavramı

Trigger’lar, tablo üzerinde tanımlanabilen ve bu tablo üzerinde bir işlem gerçekleştiğinde tetiklenen programlama ögeleridir. Çalışma yapısı, C#’daki event mantığı ile aynıdır.

DML ve DDL trigger’lar olarak ikiye ayırmak mümkündür.

DML (Data Manipulation Language) => Datalar üzerinde sorgulama ve değişiklik yapmak için kullandığımız SQL deyimleridir. (SELECT,INSERT, UPDATE ve DELETE)

DDL (Data Definition Language) =>  CREATE, ALTER ve DROP komutlarına verilen isimdir.

Bu yazımızda DML Trigger’lar üzerinde inceleme yapacağız. Insert, Update ve Delete işlemleri için arka planda oluşan ‘insterted’ ve ‘deleted’ tablolarını kullanacağız. Bu tablolar hakkında daha detaylı bilgiye şuradan erişebilirsiniz.

DML Trigger’ları da kendi içinde AFTER(ya da FOR) ve INSTEAD OF olmak üzere ikiye ayrılırlar. ( AFTER ve FOR birebir aynı anlamdadır. FOR anahatar sözcüğü, eski SQL sürümlerindeki alışkanlığı devam ettirmek amacı ile kullanılabilir kılınmıştır.)

AFTER => Bu trigger’lar tetiklendiğinde işlem gerçekleşmiş demektir. Yani yazdığımız trigger ‘after delete’ şeklindeyse, o tablo üzerinde data silindikten sonra bu trigger devreye girer.

INSTEAD OF => Bu trigger’lar tetiklendiğinde işlem gerçekleşmemiştir. Yani yazdığımız trigger ‘instead of delete’ şeklindeyse, o tablo üzerindeki kayıt silme işlemi yerine, bu trigger devreye girecektir.

Tanımlarken biraz karmaşık gelse de örnekler üzerinde daha iyi anlaşılacağını düşünüyorum. İlk olarak AFTER Trigger’ı ile ilgili inceleme yapalım.

Örneğimize başlamadan önce, Urun ve Siparis adında iki adet tablo oluşturalım.

CREATE TABLE Urun

(

      UrunID INT PRIMARY KEY IDENTITY(1,1),

      UrunAd NVARCHAR(50),

      StokMiktar INT

)

 

CREATE TABLE Siparis

(

      SiparisID INT PRIMARY KEY IDENTITY(1,1),

      UrunID INT,

      SiparisMiktar INT

)

Şimdi de bu iki tablo arasında ilişki olduğunu, Siparis kolonundaki UrunID’nin, Urun tablosunda referans olacağını belirtelim.

ALTER TABLE Siparis

ADD CONSTRAINT FK_Siparis_UrunID FOREIGN KEY(UrunID)

REFERENCES Urun(UrunID)

Bu constraint(kısıtlama) sayesinde, Siparis tablosundaki UrunID kolonuna, Urun tablosunda kayıtlı olmayan bir ID girişi yapılamaz.

Şimdi de Urun tablomuza birkaç adet ürün insert edelim.

INSERT INTO Urun VALUES('Kitap',500)

INSERT INTO Urun VALUES('Defter',740)

INSERT INTO Urun VALUES('Silgi',220)

Urun tablosundaki görüntümüz aşağıdaki gibidir.

Untitled

Görüldüğü gibi, Urun tablomuza 3 adet ürün girişi yaptık ve bu ürünlerin stok miktarlarını tutuyoruz. Şimdi de Siparis tablosuna insert yapalım. Ancak siparişi verirken, girmiş olduğumuz sipariş miktarı kadar, Urun tablosunda o ürün için tutulan StokMiktarının da azalmasını istiyoruz. Bunun için bir trigger yazalım. Bahsettiğimiz olay, Siparis tablosuna data girişi yapıldığında tetiklenmelidir. Dolayısı ile AFTER INSERT için bir trigger yazmalıyız.

CREATE TRIGGER TRG_StokDusur

ON Siparis

AFTER INSERT

AS

      DECLARE @urunId INT

      DECLARE @siparisMiktar INT

     

      SELECT @urunId=UrunID,@siparisMiktar=SiparisMiktar FROM inserted

     

      UPDATE Urun SET StokMiktar=StokMiktar-@siparisMiktar

      WHERE UrunID=@urunId

Artık Siparis tablosunda yapılan her insert’den sonra, bu trigger devreye girecektir. Şimdi de Siparis tablosuna insert yapalım.

INSERT INTO Siparis VALUES(1,100) --ID'si 1 olan Kitap adlı ürün için 100 adet sipariş verdik.

Yukarıdaki kodu çalıştırdığımızda aşağıdaki mesajlı alırız.

Untitled2

Görüldüğü gibi, yapmış olduğumuz insert 2 farklı tabloyu etkilemiş durumda. Yazdığımız trigger sayesinde Urun tablosundaki StokMiktarı da update edilmiş oldu.

Urun tablosundaki kayıtları tekrar görüntüleyelim.

Untitled3

İstediğimiz sonucu elde etmiş durumdayız. İik durumda  Kitap adlı üründen 500 adet mevcuttu. Ancak 100 adet sipariş verdiğimizde, Urun tablosundaki stoktan da 100 adet düşüldüğünü ve Stok miktarının 400 olduğunu görüyoruz.

Şimdi de Kitap ve Arşiv adında iki tablo oluşturalım.

CREATE TABLE Kitap

(

      KitapID INT PRIMARY KEY IDENTITY(1,1),

      KitapAd NVARCHAR(50),

      Yazar NVARCHAR(50)

)

 

CREATE TABLE Arsiv

(

      KitapID INT,

      KitapAd NVARCHAR(50),

      Yazar NVARCHAR(50),

      SilindigiTarih DATETIME

)

Kitap tablomuza birkaç adet insert yapalım.

INSERT Kitap VALUES('Melekler ve Şeytanlar','Dan Brown')

INSERT Kitap VALUES('Gece Gelen Ölüm','Agatha Christie')

Amacımız, Kitap tablosundan bir kayıt silindiğinde, silinen datanın o anki tarih bilgisi ile birlikte Arsiv tablosuna kaydedilmesini sağlayan bir trigger yazmak.

CREATE TRIGGER TRG_ArsiveTasi

ON Kitap

AFTER DELETE

AS

      INSERT INTO Arsiv

            SELECT *,GETDATE() FROM deleted

Şimdi de Kitap tablosundaki ‘Melekler ve Şeytanlar’ adlı kaydı silelim.

DELETE Kitap WHERE KitapID=1

Yukarıdaki örnekte olduğu gibi, yaptığımız işlemin 2 tablo üzerinde etkili olduğuna dair bir mesaj alacağız.

Untitled2

Arşiv tablomuzu görüntülediğimizde, sildiğimiz datanın buraya kaydedildiğini görebiliriz.

Untitled4

Şimdi de INSTEAD OF Trigger’ları ile ilgili bir örnek yapalım. Araba adında bir tablo oluşturalım.

CREATE TABLE Araba

(

      ArabaID INT PRIMARY KEY IDENTITY(1,1),

      Marka NVARCHAR(20),

      Model NVARCHAR(20),

      Aktif BIT

)

 

ALTER TABLE Araba

ADD CONSTRAINT DF_Araba_Aktif DEFAULT 1 FOR Aktif

Not: Aktif adlı kolonun tipi BIT olduğundan, 0 veya 1 değerlerini tutabilir. Yazdığımız constraint(kısıtlama) ile bu kolona değer girilmesse, Default olarak 1 değerini verilmesini sağladık.

Oluşturduğumuz tabloya birkaç data girişi yapalım.

INSERT INTO Araba(Marka,Model) VALUES('Renault','Megane')

INSERT INTO Araba(Marka,Model) VALUES('Ford','Focus')

INSERT INTO Araba(Marka,Model) VALUES('Audi','A3')

Araba adlı tablomuzun görüntüsü aşağıdaki gibidir.

Untitled5

Insert ettiğimiz datalar için Aktif kolonuna değer girmememize rağmen, istediğimiz gibi default olarak 1 gelmiş durumda. Şimdi de bu tablo üzerinde yapılacak bir delete işlemi için bir trigger yazalım. Yazdığımız trigger, kaydı silmeyerek, Aktif kolonuna 0 değerini atasın. Yani silme işlemi yerine update işlemi yapacağız. Dolayısı ile delete işleminin yerine başka bir iş yaptırmış olacağız.

CREATE TRIGGER TRG_PasifYap

ON Araba

INSTEAD OF DELETE

AS

      UPDATE A SET Aktif=0 FROM Araba A

      JOIN deleted D ON A.ArabaID=D.ArabaID

Araba tablosundan bir kayıt silelim ve tabloyu görüntüleyelim.

DELETE Araba WHERE ArabaID=2

Untitled6

Görüldüğü gibi yapmış olduğumuz silme işlemi gerçekleşmedi. Onun yerine, yazmış olduğumuz trigger devreye girerek, silinmek istenen datanın Aktif kolonundaki değerini 0’a update etti.

Faydalı olması dileği ile…



20 Nisan 2011 Çarşamba

SQL Output Kavramı

Bu yazımızda insert, update ve delete işlemleri için arka planda yapılan işlemleri inceliyor olacağız. SQL’de bu komutları yazdığımız an, arka planda otomatik olarak bir transaction başlatılır (bizim ele aldığımız durumlar hariç). Bu transaction içerisinde, yine arka planda sanal olarak “inserted” ve “deleted” tablosu oluşturulur. Kodumuzu çalıştırdığımız zaman da, bu transaction kapatılarak işlem sonlanır.

Normal şartlarda, bu sanal tabloları sadece trigger’lar içerisinde kullanabiliyorduk. Ancak SQL Server 2005’den itibaren,  bu tabloları, yazdığımız kodlar içerisinde ele alabilmemiz mümkün hale geldi. Şimdi de bu tabloların ne amaçla kullanıldıklarını anlamaya çalışalım.

INSERT => Insert etmeye çalıştığımız data, ilk olarak arka plandaki sanal olarak oluşan ‘inserted’ tablosuna gider. Buradan da insert etmek istediğimiz tabloya kaydedilir ve oluşan bu sanal tablo, SQL belleğinden düşer.

DELETE => Silmek istediğimiz data, ilk olarak arka plandaki sanal olarak oluşan ‘deleted’ tablosuna gider. Daha sonra silinir ve ‘deleted’ tablosu SQL belleğinden düşer.

UPDATE => Update etmek istediğimiz datanın eski hali ‘deleted’ tablosuna gider, yeni hali de ‘inserted’ tablosuna kaydedilir. Daha sonra datanın son hali, içerisinde bulunduğu tablo içerisine kaydedilir ve data güncellenmiş olur.

'‘inserted’ ve ‘deleted’ tablolarına, trigger yazarken ihtiyaç duyarız. Çünkü insert, update ve delete işlemleri yapıldığında, başka bir işi tetiklemek isteyebiliriz. Dolayısı ile oluşan datalara ihtiyaç duyarız. Şimdi örnek üzerinde inceleme yapalım.

Üzerinde çalışabileceğimiz basit bir tablo tasarlayarak örneğimize başlayalım.

CREATE TABLE Personel

(

      PersonelId INT IDENTITY(1,1),

      AdSoyad NVARCHAR(100),

      Maas MONEY

)

ilk olarak insert işlemi sırasında, inserted tablosundaki dataları gösterelim. Bir tablo oluşturarak, inserted tablosundaki kayıtları buraya kopyalayacağız. Girişte de bahsettiğimiz gibi, insterted tablosu geçici bir tablodur. İşlem tamamlandığında SQL belleğinde tutulmaz.

DECLARE @Kaydedilenler TABLE

(

      PersonelId INT,

      AdSoyad NVARCHAR(100),

      Maas MONEY

)

 

INSERT INTO Personel

      OUTPUT inserted.PersonelId, inserted.AdSoyad, inserted.Maas INTO @Kaydedilenler

VALUES('Ali Deneme',100)

 

SELECT * INTO InsertTablomuz FROM @Kaydedilenler

SELECT * FROM InsertTablomuz

@Kaydedilenler adında bir tablo tanımladık. Daha sonra insert ettiğimiz datayı, sanal tablo olan ‘inserted’dan bu tabloya attık. Buradan da bulk copy yaparak, @Kaydedilenlerdeki datayı kalıcı olarak saklamak amacı ile ‘InsertTablomuz’ adlı tabloya attık. Yukarıdaki kodu çalıştırdığımızda karşımıza gelecek ekran görüntüsü, ‘InsertTablomuz’ adlı tablonun görüntüsü olacaktır. Personel tablosuna insert ettiğimiz data, aynı zamanda ‘InsertTablomuz’ adlı tabloda da kayıtlı durumdadır.

NOT: ‘InsertedTablomuz’ adlı tabloyu manuel olarak tanımlamadık. Bulk copy yaparak, @Kaydedilenler adlı tablo birebir ile aynı kolonlara ve aynı dataya sahip halde oluşturduk.

Şimdi de delete işlemi için bir örnek yapalım. Yukarıdaki insert işleminin ardından, Personel tablosunda PersonelID’si 1 ve Adı ‘Ali Deneme’ olan bir kayıt mevcuttur. Bu kaydı silip, deleted tablosundan da faydalanarak ‘SilinenlerTablomuz’ adlı tabloya atalım.

DECLARE @Silinenler TABLE

(

      PersonelId INT,

      AdSoyad NVARCHAR(100),

      Maas MONEY

)

 

DELETE Personel

      OUTPUT deleted.PersonelId, deleted.AdSoyad, deleted.Maas INTO @Silinenler

WHERE PersonelId=1

 

SELECT * INTO SilinenlerTablomuz FROM @Silinenler

 

SELECT * FROM SilinenlerTablomuz

Bu işlemin ardından, Personel tablosundaki kaydın silindiğini görebiliriz. Ancak yaptığımız işlemler sayesinde silinen bu data ‘SilinenlerTablomuz’ adlı tabloya kaydedilmiştir. (‘SilinenlerTablomuz’, bulk copy ile oluşturuldu.)

Şimdi de update işlemi için bir örnek ele alalım. Personel tablosuna birkaç tane insert yapalım.

INSERT INTO Personel VALUES('Ali Deneme',1000)

INSERT INTO Personel VALUES('Ayşe Test',2000)

INSERT INTO Personel VALUES('Fatma Tost',1400)

INSERT INTO Personel VALUES('Veli Deneme',5000)

Tüm personellerin maaşına 500 TL zam yapmak isteyelim. Ancak yaptığımız bu değişikliğin başka bir tabloda tutulmasını da istiyoruz. (Eski maaş, yeni maaş ve tarih bilgileri ile birlikte)

DECLARE @ZamTablo TABLE

(

      PersonelId INT,

      AdSoyad NVARCHAR(100),

      EskiMaas MONEY,

      YeniMaas MONEY,

      Tarih DATETIME

)

 

UPDATE Personel SET Maas=Maas+500

      OUTPUT inserted.PersonelId,inserted.AdSoyad,deleted.Maas [Eski Maas],inserted.Maas [Yeni Maas],GETDATE() INTO @ZamTablo

 

SELECT * INTO ZamListesi FROM @ZamTablo

 

SELECT * FROM ZamListesi

Burada dikkat edilmesi gereken kısım, OUTPUT anahtar sözcüğünden sonra yazdığımız kısımdır. Oluşturduğumuz @ZamTablo adlı değişkene deleted ve inserted tablosundan data alıyoruz. PersonelId ve AdSoyad zaten bu iki tabloda da aynı olduğundan, bu bilgileri inserted veya deleted tablosundan çekmemiz sonucu değiştirmez. Ancak maaş bilgisi update edilen kolondur. Girişte bahsettiğimiz gibi, update durumunda eski datalar deleted tablosuna, yeni datalar da inserted tablosuna kaydolur. PersonelId’si 1 olan kayıt için konuşursak, deleted tablosunda “1,Ali Deneme,1000” datası mevcuttur. inserted tablosunda ise datanın update edilmiş hali, yani ‘1,Ali Deneme,1500’ datası mevcuttur. Bu sebepten dolayı eski maaşı deleted  tablosundan, yeni maaşı ise inserted  tablosundan elde ettik.

Bahsi geçen sanal tablolardan data aktarımlarını bu şekilde yapabilmek mümkün. Ama başta da söylediğimiz gibi, bu tablolara çoğunlukla trigger yazarken ihtiyaç duyarız. Yukarıda bahsettiğimiz çalışma mantığını kavrayabilmemiz, trigger’ları ele alırken işimizi kolaylaştıracaktır.



15 Nisan 2011 Cuma

SQL Table-valued Functions

Adından da anlaşıldığı gibi, geriye tablo dönen fonksiyonlardır. Sorgu içerisinde kullanılabildikleri gibi, FROM anahtar sözcüğünden sonra referans olarak da verilebilirler. Table-valued Function’lar, InLine ve MultiLine olmak üzere ikiye ayrılırlar.

IN-LINE

Parametre alırlar. Üzerinde insert, update ve delete işlemleri yapılabilir. Geriye döndürdüğü tablo tip(kolon sayısı,boyutu vs) olarak, sorgu içerisinde return edilen tablo ile birebir aynıdır.

AdventurWorks veritabanında, SubCategoryID’si 1 olan ürünleri listelemek isteyelim. Bunun için bir view yazmamız yeterlidir.

CREATE VIEW VW_ProductBySubCatId_I

AS

      SELECT * FROM Production.Product

      WHERE ProductSubcategoryID=1

Şimdi de SubCategoryID’si 2 olan ürünleri listelemek isteyelim. Bunun için ayrı bir view yazmamız gerekecek. Çünkü view’ler parametre alamazlar.

CREATE VIEW VW_ProductBySubCatId_II

AS

      SELECT * FROM Production.Product

      WHERE ProductSubcategoryID=2

Başka bir SubCategoryID’ye göre listelemek istersek tekrar tekrar view yazmamız gerekecek. Ancak bütün bunların yerine, parametre olarak SubCategoryID alan ve geriye tablo dönen bir fonksiyon yazmamız bizim için daha iyi bir çözüm olacaktır.

CREATE FUNCTION FN_GetProductBySubCat(@subCatId INT)

RETURNS TABLE

AS

      RETURN

            SELECT * FROM Production.Product

            WHERE ProductSubcategoryID=@subCatId

SubCategoryID’si 1 olan ürünleri elde etmek için,

SELECT * FROM FN_GetProductBySubCat(1)

Untitled

Production.Product tablosunun tüm kolonlarını istediğimiz filtreleme işlemine göre elde ettik. Yani elimizde sadece SubCategoryID’si 1 olan ürünlerin satırları mevcut. Sonuc tablomuz tip olarak, Productin.Product tablosu ile birebir aynı.

MULTI-LINE

Returns kısmında ‘TABLE’ yazmak yerine, manuel olarak tablo oluşturup, sonuçları bu tabloya insert ederiz.

Yukarıdaki aynı örneği multi-line function ile yapalım. Buradaki fark; geri dönecek olan tabloyu manuel olarak set edebilmemizdir. Ayrıca fonksiyon içerisinde extra kodlamalar da yapabiliriz.(cursor kullanmak gibi…)

CREATE FUNCTION FN_GetProductBySubCat_Multi(@subCatId INT)

RETURNS @TABLO TABLE

(

      ProductId INT,

      Name NVARCHAR(50),

      ListPrice MONEY,

      Color NVARCHAR(100)

)

AS

      BEGIN

            INSERT INTO @TABLO

                  SELECT ProductID,Name,ListPrice,Color FROM Production.Product

                  WHERE ProductSubcategoryID=@subCatId

            RETURN

      END

Artık sonuç tablomuzda sadece Id,Ad,Fiyat ve Renk bilgisi kolonları bulunacak.

SELECT * FROM FN_GetProductBySubCat_Multi(1)

Untitled2

Aldığımız sonuç in-line fonksiyon yazdığımızdaki ile aynı. Ancak orada, sonuç kümesindeki kolon sayısı Production.Product tablosundaki kolon sayısı ile aynıdır. Yani sonuç kümemize dahil etmek istemediğimiz kolonları da elde etmiş olduk. Multi-line daki fark ise , kolonları manuel olarak oluşturabilmemizdir. Dolayısı ile elimizdeki toplam kolon sayısı 4’tür ve sonuç kümemize istemediğimiz kolonları dahil etmemiş olduk.

Şimdi başka bir örnek yapalım. Person.Contact tablosunda, kişilere ait iletişim bilgileri tutulmaktadır. Yazacağımız fonksiyon parametre olarak metinsel bir ifade alsın. Eğer parametre ‘UZUN’ ise, kişinin tam adını (ünvan+ad+soyad), mail adresini ve telefon bilgisini dönsün. Eğer ‘KISA’ ise, sadece soyadını, mail adresini ve telefon bilgisini dönsün.

CREATE FUNCTION FN_GetContactInformation(@Len VARCHAR(10))

RETURNS @TABLO TABLE

(

      Name NVARCHAR(100),

      Email NVARCHAR(50),

      Phone NVARCHAR(25)

)

AS

      BEGIN

            IF @Len = 'UZUN'

            BEGIN

                  INSERT INTO @TABLO

                        SELECT ISNULL(Title,'') + ' ' + FirstName + ' ' + ISNULL(MiddleName,'') + ' ' + LastName,EmailAddress,Phone  FROM Person.Contact

            END

            ELSE IF @Len = 'KISA'

            BEGIN

                  INSERT INTO @TABLO

                        SELECT LastName,EmailAddress,Phone FROM Person.Contact

            END

      RETURN

      END

Şimdi de istediğimiz parametreleri verelim ve sonuçları görelim.

SELECT * FROM FN_GetContactInformation('UZUN')

Untitled3

SELECT * FROM FN_GetContactInformation('KISA')

Untitled4

Parametre olarak bu iki metini dışında bir değer girilirse, sonuç kümemiz boş bir tablo olacaktır.