SQL Server Trigger (Tetikleyici) Nedir? Nasıl Oluşturulur?

Bu makalemde triggerın işlevinden, neden kullandığımızdan, trigger türlerinden bahsedip bir örnek üzerinden açıklamaya çalışacağım.

SQL Server’da triggerlar, veri tabanı nesnesine, veri tabanına ve sunucu olaylarına yanıt oluşturup otomatik olarak yürütülen özel bir stored procedure diyebiliriz(Saklı yordam). Stored Procedureden farklı olarak triggerlar kullanıcılar tarafından tetiklenemez. Yapılan değişiklikleri loglayıp kontrol altında tutmamıza, kritik veri tabanı ya da tablolarda işlem yapılmamasını istiyorsak bunu engellemeye ve başarılı olarak açılan oturumları da kayıt altına alıp yetkilerini gözden geçirebilmemize yardımcı olur.

SQL Server tetikleyicileri inceleyelim:

• DML(Data Manipulation Language/Veri İşleme Dili) Triggers
  

  Tablolarda gerçekleşen INSERT, UPDATE ve DELETE işlemlerine karşı otomatik olarak çağrılan triggerlardır. DML triggerlar After/For Trigger ve Instead Of Trigger olarak ayrılır.

  After/For Trigger: Veri tabanında yapılan DML işlemlerinin tamamlanmasından sonra tetiklenir.

  Instead Of Trigger: DML işlemi gerçekleşmeden önce ne yapılması gerektiğini belirttiğimiz triggerlardır.

• DDL(Data Definition Language/ Veri Tanımlama Dili) Triggers
  

  Veri tabanında gerçekleşen CREATE, ALTER ve DROP işlemlerine karşı otomatik olarak tetiklenir.

• Logon Triggers
  

  Başarılı oturum açma durumlarında tetiklenir.

Trigger Oluşturma Söz Dizimi

Kısaca bu şekilde triggerlarımızı create edebiliriz.

DML Triggers

CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name 
ON { table } 
[ WITH  [ ,...n ] ] 
{ FOR | AFTER } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
AS { sql_statement [ ; ] [ ,...n ] }

dml_trigger_option ::= 
[ NATIVE_COMPILATION ] 
[ SCHEMABINDING ] 
[ EXECUTE AS Clause ]

DDL Triggers

CREATE [ OR ALTER ] TRIGGER trigger_name   
ON { ALL SERVER | DATABASE }   
[ WITH  [ ,...n ] ]  
{ FOR | AFTER } { event_type | event_group } [ ,...n ]  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME   [ ; ] }  
  
ddl_trigger_option::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]

Logon Triggers

CREATE [ OR ALTER ] TRIGGER trigger_name   
ON ALL SERVER   
[ WITH  [ ,...n ] ]  
{ FOR| AFTER } LOGON    
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME   [ ; ] }  
  
 logon_trigger_option ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]

Şimdi bir DML Triggerı nasıl oluşturulur örnek üzerinden görelim.

Önce DML işlemleri yapıldığında bunların detayını yazacak bir tabloyu ilgili veri tabanında CREATE ediyoruz.

-- Step 1

CREATE TABLE [dbo].[EVENT_LOG]
(
[Id] int IDENTITY(1,1) NOT NULL,
[EventDate] [datetime] NOT NULL,
[DMLEvent] [varchar](20) NOT NULL,
[UserName] [nvarchar](256) NOT NULL,
[AppName] [nvarchar](128) NOT NULL,
[HostName] [nvarchar](128) NOT NULL,
[SchemaName] [sysname] NOT NULL,
[ObjectName] [sysname] NOT NULL,
[EventXML] [xml] NULL,
CONSTRAINT [PK_LTC_Id] PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]
GO

Daha sonra tablo içerisindeki kolonlara default değerlerini atıyoruz.

-- Step 2

ALTER TABLE [dbo].[EVENT_LOG] ADD CONSTRAINT [DF_EL_EventDate] DEFAULT (getdate()) FOR [EventDate];

ALTER TABLE [dbo].[EVENT_LOG] ADD CONSTRAINT [DF_EL_DMLEvent] DEFAULT ('') FOR [DMLEvent];

ALTER TABLE [dbo].[EVENT_LOG] ADD CONSTRAINT [DF_EL_UserName] DEFAULT (coalesce(suser_sname(),'?')) FOR [UserName];

ALTER TABLE [dbo].[EVENT_LOG] ADD CONSTRAINT [DF_EL_AppName] DEFAULT (coalesce(app_name(),'?')) FOR [AppName];

ALTER TABLE [dbo].[EVENT_LOG] ADD CONSTRAINT [DF_EL_HostName] DEFAULT (coalesce(host_name(),'?')) FOR [HostName];

GO

DML komutları için [dbo].[Customer] tablosunda triggerımızı oluşturuyoruz. Eğer bu tabloda DML işlemlerini engellemek isteseydik ROLLBACK TRANSACTION diyebilirdik.

--Step 3 (CREATE DML TRIGGER)

CREATE TRIGGER [dbo].[TRG_Customer] ON [dbo].[Customer]
FOR INSERT, UPDATE, DELETE AS
BEGIN

-- Insert
IF EXISTS (Select * From inserted) AND NOT EXISTS (Select * From deleted)
BEGIN
INSERT [dbo].[EVENT_LOG] ([EventDate], [SchemaName], [ObjectName], [EventXML])
SELECT 'INSERT', '[dbo]', '[Customer]', (SELECT * FROM inserted as Record for xml auto, elements , root('RecordSet'), type)
RETURN;
END

-- Delete
IF EXISTS (select * from deleted) AND NOT EXISTS (select * from inserted) 
BEGIN INSERT [dbo].[EVENT_LOG] ([EventDate], [SchemaName], [ObjectName], [EventXML]) 
SELECT 'DELETE', '[dbo]', '[Customer]', (SELECT * FROM deleted as Record for xml auto, elements , root('RecordSet'), type) 
RETURN; 
END

-- Update
IF EXISTS (select * from inserted) AND EXISTS (select * from deleted) 
BEGIN INSERT [dbo].[EVENT_LOG] ([EventDate], [SchemaName], [ObjectName], [EventXML]) 
SELECT 'UPDATE', '[dbo]', '[Customer]', (SELECT * FROM deleted as Record for xml auto, elements , root('RecordSet'), type) 
RETURN; 
END 
END; 
GO

Oluşturduğumuz tabloyu ve [dbo].[Customer] tablosu üzerinde yapılacak DML işlemleri için triggerımızı gözlemliyoruz. 

Resim-1

Birkaç DML işlemleri uygulayıp triggerımızı test edelim.

INSERT [dbo].[Customer]
([Name], [Surname], [Gender], [Job], [Province])
VALUES ('Arzu',
'Duran',
'K',
'Database Administrator',
'İSTANBUL')
GO

UPDATE [dbo].[Customer] 
SET [Name] = 'Arzu' 
WHERE CustomerId = 1 
GO 

DELETE FROM [dbo].[Customer] 
WHERE [CustomerID] = 26 
GO

Scriptlerimizi çalıştırdıktan sonra oluşturduğumuz [dbo].[EVENT_LOG] tablosunu inceleyelim.

SELECT
 [Id]
,[EventDate]
,[DMLEvent]
,[Username]
,[AppName]
,[HostName]
,[SchemaName]
,[ObjectName]
,[EventXML]
FROM [DEMO].[dbo].[EVENT_LOG]

Sorgu sonucunda uygulamış olduğumuz DML işlemlerinin detaylarının [dbo].[EVENT_LOG] tablosuna yazıldığını gözlemliyoruz. Değişikliğin yapıldığı tarih, yapılan DML işlemi, değişikliği yapan kişi, değişikliğin yapıldığı uygulama ve Host name, hangi şema ve tabloya ait kayıtlarda değişiklik yapıldığı ve bu değişikliğin detaylı bilgisini bu log tablomuzda gözlemleyebiliyoruz.

Resim-2

Bu konuyla ilgili sorularınızı alt kısımda bulunan yorumlar alanını kullanarak sorabilirsiniz.

Referanslar

https://www.mshowto.org

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver15

TAGs:SQL Server , SQL Server Trigger, SQL Server Trigger Oluştuma, Trigger Kullanımı,Trigger Örneği, DML Trigger, DDL Trigger, Logon Trigger, After Trigger, For Trigger, Instead of Trigger, Tetikleyici, Trigger Syntax, Update Trigger, Insert Trigger, Delete Trigger, History Log, Create Trigger