MSSQL’de trigger yazma

Trigger(Tetikleyici), SQL’de bir sorgu çalıştırdığımızda, başka bir sorgunun çalışmasını sağlamak için yazılan koddur. Bir tablodan kayıt eklendiğinde/silindiğinde/güncellendiğinde, başka bir tablodan onunla ilgili bir kaydın eklenmesi/silinmesi/güncellenmesi için kullanabiliriz. Triggerlar; insert, update, delete sorguları içindir.

CREATE TRIGGER isim { BEFORE | AFTER } { olay [ OR ... ] }
    ON tablo [ FOR [ EACH ] { ROW | STATEMENT } ]
    EXECUTE PROCEDURE işlev_ismi ( argümanlar )

Parametreler
isim
Yeni tetiği belirtecek isim. Bu isim, aynı tablodaki diğer tetiklerin isimlerinden farklı olmalıdır.
BEFORE
AFTER
İşlevin olaydan önce mi sonra mı çağrılacağını belirler.
olay
INSERT, UPDATE veya DELETE’den biri; tetiği çalıştıracak olayı belirtmek için kullanılır. OR kullanarak çok sayıda olay belirtilebilir.
tablo
Tetiği içerecek tablonun ismi (şema nitelemeli olabilir).
FOR EACH ROW
FOR EACH STATEMENT
Tetik işlevinin tetikleyici olay tarafından her satır için bir kere mi yoksa her SQL deyimi için bir kere mi çalıştırılacağını belirler. Eğer hiçbiri belirtilmemişse, FOR EACH STATEMENT öntanımlıdır.
işlev_ismi
Tetikle çalıştırılan, kullanıcı tarafından argümansız ve dönüş türü trigger olarak bildirilmiş ve tanımlanmış bir işlev.
argümanlar
Tetik tarafından çalıştırıldığında işleve aktarılacak isteğe bağlı argümanların virgülle ayrılmış listesi. Argümanlar dizge sabitlerdir. Basit isimler ve sayısal sabitler de burada yazılabilir, fakat her biri dizgeye dönüştürülecektir. Tetikleyici işlev dilinin nasıl gerçeklendiğini incelemeniz ve işlev içinde tetik argümanlarına nasıl erişildiğini öğrenmeniz önerilir; normal işlev argümanlarından farklı ele alınıyor olabilirler.
İnsert Trigger

create trigger stokGuncelle on satis –bu satırda trigger adını yazıyoruz ve tetiklemenin kaynağı olan tabloyu yazıyoruz.
for insert – trigger’ın hangi komut için yazıldığını gösterir.
as
begin
   declare @satisAdedi int, @urunid int – iki adet değişken belirliyoruz.
   select @satisAdedi=satisadedi, @urunid=urunid from inserted –yaptığımız insert iþşeminde satisadedi ve urunid değerlerini alıp bunları daha önce belirlediğimiz değişkenlere atıyoruz.
   update urunler set stokadedi=stokadedi-@satisAdedi where urunid=@urunid –şimdi aldığımız değişkenleri başka bir tabloda değişiklik yapmak için kullanıyoruz.
end

Tanımlı olan triggerları aşağıdaki sql cümleleri ile enable veya disable edebiliriz.

DISABLE TRIGGER tr_UrunSil ON Urunler
ENABLE TRIGGER tr_UrunSil ON Urunler

Başka bir örnek olarak silinen ürün sipariş tablosunda mevcut ise silme işlemini iptal edelim.

CREATE TRIGGER tr_UrunSil ON Urunler
AFTER DELETE
AS
BEGIN
DECLARE @Uid INT 
SELECT @Uid= UrunID FROM DELETED
IF EXISTS(SELECT * FROM Siparisler WHERE UrunID=@Uid) 
BEGIN 
Print 'Ürün siparişlerde mevcut, silinenmez'
ROLLBACK TRANSACTION 
RETURN
END
END

Update Trigger
Bu kod ile, satış tablosunda bir satırı güncellediğimizde, ürünler tablosunda o ürünün stok adedi değiştiriliyor. Update aslında iki basamakta gerçekleşir. Birinci adımda güncelleme yapacağımız satır silinir, ikinci adımda ise o satır yeniden eklenir ama bu sefer güncel değerleri eklenir. İlk olarak silme işleminde silinen değerleri alıyoruz. Daha sonra ekleme işleminde eklenen değerleri de yeni değerler olarak alıyoruz. Sonra bu değerleri karşılaştırarak değişim miktarına göre ürünlerdeki stok adedini de güncelliyoruz.

create trigger duzelt on satis
for update– update işlemi ile tetiklemenin başlatıldığını gösterir.
as
begin
      declare @yenisatisadedi int, @satisadedi int, @urunid int
      select @satisAdedi=satisadedi, @urunid=urunid from deleted –silme işleminde silinen satırdaki satisadedi ve urunid değerlerini alıyoruz.
      select @yenisatisadedi=satisadedi , @urunid=urunid from inserted –ekleme işleminde eklenen satisadedi ve urunid değerlerini alıyoruz.
      if(@yenisatisadedi<@satisadedi) –eğer eklenen satisadedi değeri silinen satisadedi değerinden küçük ise işlem yapar. yani güncelleme yaptığımız değer eski değerden az ise.
      begin
            update urunler set stokadedi=stokadedi-(@satisadedi-@yenisatisadedi) where urunid=@urunid – ürünlerdeki stokadedini güncelleme yaptığımızda satisadedi değerindeki değişim kadar azaltır.
      end
      else
      begin
            update urunler set stokadedi=stokadedi+(@yenisatisadedi-@satisadedi)
            where urunid=@urunid– ürünlerdeki stokadedi'ni güncelleme yaptığımızda satisadedi değerindeki değişim kadar artırır.
      end
end

Kullanıcının birden fazla kayıt silmesini engellemek için yazılan bir delete trigger örneği

CREATE TRIGGER trigger_Silindi --Trigger adı
ON Kisiler --İlgili Tablo
for DELETE --Oluşturulacak trigger türü
as
declare @sayi int
select @sayi = count(*) from deleted --Silme işlemi için deleted (Silinmişler)
--tablosuna gönderilen kayıt sayısı
if @sayi>1
begin
rollback transaction
end

Store Procedure T-SQL ile arama

Dinamik arama sorgularında t-sql kullanmak işimizi kolaylaştıracaktır. T-sql kullanmaktaki amaç where kıstaslarının dinamik olarak eklemektir. Fikir edinmeniz için, kullandığım bir t-sql arama sorgusu aşağıdadır. Entity framework ile kullanıldığında sadece sorgulamak istediğim değişkenlere değerlerini göndermem yeterli oluyor.

USE [DatabaseEx]
GO
/****** Object:  StoredProcedure [dbo].[sp_UyeArama]    Script Date: 02/12/2013 11:36:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Cem ELMA
-- Create date: 07.02.2013
-- Description:	Üyeler tablosunda arama
-- =============================================
ALTER PROCEDURE [dbo].[sp_UyeArama]
@unvan nvarchar(255)=null,
@sicilno nvarchar(20)=null,
@sehir nvarchar(3)=null,
@ilce nvarchar(2)=null,
@semt nvarchar(2)=null,
@meslekkomitekod nvarchar(5)=null,
@isciaralik int=null,
@sermayearalik int=null,
@ortaklik nvarchar(500)=null,
@kayiticindeara nvarchar(255) = null,
@siralamatipi int=null
AS
BEGIN
    SET NOCOUNT ON;
    --Sorgu Tanımlamalar
    Declare @SQLQuery AS NVarchar(4000)

    --Dinamik sorguyu oluşturur
    Set @SQLQuery = 'SELECT * from Table1 WHERE (1=1) ' 
    if @kayiticindeara Is Null
    begin

    if @unvan Is Not Null Set @SQLQuery = @SQLQuery + ' And LOWER(SBFRMUNV) LIKE ''%' + @unvan + '%'''		
    if @sicilno Is Not Null Set @SQLQuery = @SQLQuery + ' And SBSCLKOD LIKE ''%' + @sicilno + '%'''		
    if @sehir Is Not Null Set @SQLQuery = @SQLQuery + ' And SBILKODU LIKE ''%' + @sehir + '%'''
    if @ilce Is Not Null Set @SQLQuery = @SQLQuery + ' And SBILCEKD LIKE ''%' + @ilce + '%'''	
    if @semt Is Not Null Set @SQLQuery = @SQLQuery + ' And SBSEMTKD LIKE ''%' + @semt + '%'''	
    if @meslekkomitekod Is Not Null Set @SQLQuery = @SQLQuery + ' And SBMESKOMKOD = @meslekkomitekod' --Grup
    if @isciaralik Is Not Null
	begin
            SET @SQLQuery = CASE
            WHEN @isciaralik = 25 THEN @SQLQuery + ' And (SBISCSYS >= 5 AND SBISCSYS < 26)'
            WHEN @isciaralik = 50 THEN @SQLQuery + ' And (SBISCSYS >= 26 AND SBISCSYS < 50)'
            WHEN @isciaralik = 200 THEN @SQLQuery + ' And (SBISCSYS >= 50 AND SBISCSYS < 200)'
            WHEN @isciaralik = 201 THEN @SQLQuery + ' And (SBISCSYS >= 200)'
            END
	end
    if @sermayearalik Is Not Null
	begin	
	    SET @SQLQuery = CASE
            WHEN @sermayearalik = 1 THEN @SQLQuery + ' And (SNSRMYTO >= 1000000)'
            WHEN @sermayearalik = 2 THEN @SQLQuery + ' And (SNSRMYTO >= 250000 AND SNSRMYTO < 1000000)'
            WHEN @sermayearalik = 3 THEN @SQLQuery + ' And (SNSRMYTO >= 100000 AND SNSRMYTO < 250000)'
            WHEN @sermayearalik = 4 THEN @SQLQuery + ' And (SNSRMYTO >= 25000 AND SNSRMYTO < 100000)'
            WHEN @sermayearalik = 5 THEN @SQLQuery + ' And (SNSRMYTO >= 5000 AND SNSRMYTO < 25000)'
            WHEN @sermayearalik = 6 THEN @SQLQuery + ' And (SNSRMYTO >= 1 AND SNSRMYTO < 5000)'
            END  
	end
    if @ortaklik Is Not Null Set @SQLQuery = @SQLQuery + ' And LOWER(SBORTAKL) LIKE ''%' + @ortaklik + '%'''		

    end
    else 
    begin
	Set @SQLQuery = @SQLQuery + ' and (LOWER(SBFRMUNV) LIKE ''%' + @kayiticindeara + '%'' 
	    or SBSCLKOD LIKE ''%' + @kayiticindeara + '%''
	    or SBTELEF1 LIKE ''%' + @kayiticindeara + '%''
	    or SBNACEKD LIKE ''%' + @kayiticindeara + '%''
	    or SBTICSCL LIKE ''%' + @kayiticindeara + '%'') '	
    end
    --Sorgu Sıralamasını ekler
    if (@siralamatipi=1) Set @SQLQuery = @SQLQuery + ' Order By SBFRMUNV'
	else Set @SQLQuery = @SQLQuery + ' Order By SBFRMUNV desc'

	--sorguyu çalıştırır
	EXECUTE sp_executesql @SQLQuery
END