SQL Server’da Akıllı Auditing

Merhaba, yazının başlığına bakıp SQL Server’ın standart Audit yapısını anlatacağımı düşünüyorsanız sizi bir sürpriz bekliyor. Doğru audit anlatacağım ama bildiğimiz anlamda standart audit değil. Daha iyisi.

SQL Server’da Data Audit anlamında kullanılan bazı teknolojiler var.

Bunlardan birincisi SQL Server Audit. Temel anlamda bir tablo üzerindeki hareketleri loglamamızı sağlayan ve uluslararası denetleme kuruşlarının da kabul ettiği bir standart. Örneğin tablodan şu şekilde bir kayıt sildiğimizi düşünelim.

DELETE FROM WEB_ITEMS WHERE CODE=’93794′

Sistemimizde SQL Server Audit çalıştırıyorsak şayet, bu işlemi şu şekilde loglayabiliriz.

Resimde görüldüğü gibi tabloda hangi sql cümlesinin, ne zaman ve kim tarafından çalıştırıldığı bilgisini gösterir. Buradaki sorun şudur. Sistem belli konuları kayıt altına almakta belli konuları alamamaktadır.

Aşağıdaki tabloda Audit ile kayıt altına alınabilen işlemler listelenmiştir.

Bir diğer loglama teknolojisi SQL Server Change Data Capture (CDC) işlemidir. Bu konu ile alakalı da inşallah ilerleyen zamanlarda bir makale yazacağım.

Change Data Capture için tablomuza baktığımızda ise durum aşağıdaki gibidir.

Gördüğümüz gibi bu iki teknoloji de bizim taleplerimizi tam olarak karşılamamaktadır.

Bu durumda yapılan silme/değiştirme işlemini kayıt altına almak için trigger kullanmak mantıklıdır.

Trigger’lar bildiğiniz üzere herhangi bir veri maniplüasyonu olduğu zaman (insert, update, delete) otomatik çalışacak yapılardır.

Burada yapılacak işlem şudur.

1-Logların tutulacağı bir Log db oluşturma.

2-Log kaydını tutmak istediğimiz tablo için aynı isimde ve aynı formatta bir tablo oluşturma.

3-Bu tabloda otomatik artan alan var ise log database inde oluşturacağımız tabloda bu alanı otomatik artan yapmama. Çünkü bir ID ye sahip bir kayıt birden fazla update işlemi göreceği için aynı ID li kayıt birden fazla loglanacaktır.

4-Oluşturduğumuz tablo için LOG_DATE, LOG_USERNAME,LOG_HOSTNAME, LOG_ PROGRAMNAME, LOG_ACTIONTYPE, LOG_SQL, LOG_CHANGEDCOLUMNS  alanlarını ekleme.

5-Kayıt altına almak istediğimiz tablo için bir delete/update trigger’ı oluşturma. Insert trigger neden oluşturmuyoruz? Derseniz, buna gerek yok derim. Çünkü insert edilen bilgi zaten asıl tabloda var. Biz delete ve update ler ile ilgileniyoruz.

6-Bir de içerisinde 200’den fazla kolon içeren tablolar var. Bu tablolarda pratik bir şekilde hangi kolonun değiştiği bilgisini hızlıca görebilmemiz gerekir.

7-Bir çok uygulama geri tarafta çalışırken kullandığı Object Oriented Programming yapıları sebebi ile gereksiz update ler yapabilmektedir. Yani aslında değişen hiçbir şey yokken SQL Server’a aynı değerler ile update gönderebilmektedir. Bu durumda trigger gereksiz yere çalışır ve gereksiz yere log üretir. Bunun da önüne geçecek şekilde trigger yazılmalıdır.

Görüldüğü üzere trigger yazmak zahmetli,riskli ve dikkatli yapılması gereken bir işlemdir. Sadece 1 tablo için burada 7 adım yazdık. Ayrıca trigger’lar transaction’ın bir parçası olduğu için dikkatli yazılmaz ise, trigger’da bir yerlerde bir hata olduğunda yapılan işlem otomatik olarak Rollback yapılır.

Biraz gözünüzü korkuttuktan sonra aşağıdaki tabloyu gösterip tekrar bir motivasyon vereyim.:)

Şimdi sırayla bu işlemleri bir tablo için yapalım. Sonrasında ise yüzlerce tablo işin bu işi otomatik yapacak scripti de paylaşacağım.

Önce CRM isimli bir veritabanı oluşturuyorum.

Bu veritabanının altına CUSTOMERS isimli bir tablo ekliyorum.

CREATE TABLE [dbo].[CUSTOMER](
[ID] [int] IDENTITY(1,1),
	[CUSTOMERNAME] [varchar](100) ,
	[CITY] [varchar](50) ,
	[DISTRICT] [varchar](50) ,
	[BIRTHDATE] [date] ,
	[GENDER] [varchar](10) 
 
	CONSTRAINT [PK_CUSTOMER] PRIMARY KEY NONCLUSTERED 
	(
	[ID] ASC
	) 
)

Sonra da buraya kayıt ekleyelim.

INSERT INTO CUSTOMER (CUSTOMERNAME,CITY,DISTRICT,BIRTHDATE,GENDER)
VALUES('ÖMER ÇOLAKOĞLU','İSTANBUL','FATİH','1980-01-01','E')

Artık elimizde bir veritabanımız, bir tablomuz ve bir de kaydımız var.

Şimdi log veritabanımızı oluşturalım.

Ve Customer tablosu için log tablosu oluşturalım.

USE CRM_LOG
CREATE TABLE [dbo].[CUSTOMER](
	[ID] [int] ,
	[CUSTOMERNAME] [varchar](100),
	[CITY] [varchar](50),
	[DISTRICT] [varchar](50),
	[BIRTHDATE] [date],
	[GENDER] [varchar](10),
	[_LOG_DATE] [datetime] NULL, --LOG TARIHI
	[_LOG_USERNAME] [varchar](500) NULL,--İŞLEMİ YAPAN KULLANICI
	[_LOG_HOSTNAME] [varchar](500) NULL,--İŞLEM YAPILAN BİLGİSAYAR
	[_LOG_ACTIONTYPE] [varchar](10) NULL,--İŞLEM TÜRÜ UPDATE/DELETE
	[_LOG_PROGRAMNAME] [varchar](500) NULL,--İŞLEMİN YAPILDIĞI UYGULAMA
	[_LOG_SQL] [varchar](max) NULL,--İŞLEM YAPILAN SQL CÜMLESİ
	[_LOG_UPDATEDCOLUMNS] [varchar](max) NULL--DEĞİŞİM GÖREN KOLONLAR
	)

Şimdi trigger ımızı yazıyoruz.

Trigger’ın içerisinde elimden geldiğince açıklamaları yorum satırında yazdım.

CREATE TRIGGER TRG_LOG_CUSTOMER ON CUSTOMER 
AFTER UPDATE,DELETE
AS
BEGIN

/*
SQL SERVER DA TRIGGER İÇERİSİNDE DELETED VE INSERTED TABLOLARI OLUŞUR.
DELETE İŞLEMİNDE SİLİNEN KAYITLAR DELETED TABLOSUNDA TUTULUR, INSERTED TABLOSU BOŞTUR.
INSERT İŞLEMİNDE EKLENEN KAYITLAR INSERTED TABLOSUNDA TUTULUR, DELETED İŞLEMİ BOŞTUR
UPDATE İŞLEMİNDE SATIRIN YENİ BİLGİSİ INSERTED TABLOSUNDA, ESKİ BİLGİSİ INSERTED TABLOSUNDA TUTULUR.
BU BİLGİLERE GÖRE İŞLEMİN INSERT MÜ, UPDATE Mİ, YOKSA DELETE Mİ OLDUĞUNU ANLAYABİLİRİZ.
*/
SET NOCOUNT ON;
 
DECLARE @DELETECOUNT AS INT=0
DECLARE @INSERTCOUNT AS INT=0
DECLARE @UNIONCOUNT AS INT=0

DECLARE @LOG_SQL AS VARCHAR(MAX)
DECLARE @LOG_ACTIONTYPE AS VARCHAR(20)
 
SELECT @DELETECOUNT=COUNT(*) FROM DELETED 
SELECT @INSERTCOUNT=COUNT(*) FROM INSERTED

IF @DELETECOUNT=0 AND @INSERTCOUNT>0
	SET @LOG_ACTIONTYPE='I' --INSERT
IF @DELETECOUNT>0 AND @INSERTCOUNT=0
	SET @LOG_ACTIONTYPE='D' --DELETE 
IF @DELETECOUNT>0 AND @INSERTCOUNT>0
	SET @LOG_ACTIONTYPE='U' --UPDATE  

/******************************************************
KULLANICININ SON YAPTIĞI İŞLEMİ YAKALAMAK İÇİN DBCC INPUTBUFFER(@@SPID) KOMUTUNU KULLANIRIZ.
BU KOMUT BİR TABLO DÖNDÜRÜR. BURADA EVENTTYPE,PARAMETERS VE EVENTINFO ALANI GELİR.
EVENTINFO ALANI ÇALIŞTIRILAN SQL CÜMLESİDİR.
BURADA BU BİLGİYİ YAKALAMAK VE DEĞİŞKENE ATAMAK İÇİN TEMP TABLE KULLANIRIZ.
******************************************************/

CREATE TABLE #INPUTBUFFER (EVENTTYPE VARCHAR(100),PARAMETERS INT,EVENTINFO NVARCHAR(MAX))
INSERT INTO #INPUTBUFFER EXEC ('DBCC INPUTBUFFER(@@SPID)')

SELECT @LOG_SQL=EVENTINFO FROM #INPUTBUFFER

/******************************************************
DİĞER LOGLAMAK İSTEDİĞİMİZ BİLGİLER ZATEN FONKSİYON İLE YAKALANABİLİR.
"PROGRAME_NAME()" FONKSİYONU SQL SERVER'A HANGİ UYGULAMA İLE BAĞLANILDIĞINI GÖSTERİR.
CONNECTION STRING İÇERİSİNDE APPLICATION_NAME ALANINDA YAZAN ATTRIBUTE LERİ BURADAN YAKALARIZ.
BURADA WEB ÜZERİNDEN GELEN KULLANICILAR İÇİN KULLANICI IP'Sİ GİBİ BAŞKA BİLGİLER DE EKLENİP
GÖNDERİLİRSE BUNLAR DA KAYIT ALTINA ALINABİLİR.
******************************************************/

/******************************************************
SUSER_NAME() SQL'E BAĞLANAN KULLANICI ADI
HOST_NAME() BAĞLANAN MAKİNENİN ADI
GETDATE() ANLIK TARİH SAAT BİLGİSİ
******************************************************/

/****************************************************
TABLODA UPDATE İŞLEMİ VAR İSE INSERTED TABLOSUNDAKİ KAYITLAR İLE DELETED TABLOSUNDAKİ KAYITLARI
UNION İLE BİRLEŞTİRDİĞİMİZDE ELDE ETTİĞİMİZ KAYIT SAYISININ INSERTED TABLOSUNDAN BÜYÜK OLMASI GEREKİR
BUNU DA BİR DEĞİŞKENE ATACAĞIZ, UPDATE KONTROLÜNDE  LAZIM OLACAK
******************************************************/
SELECT @UNIONCOUNT=COUNT(*) FROM
(SELECT * FROM INSERTED 
UNION 
SELECT * FROM DELETED) T



/******************************************************
GERİYE UPDATE EDİLEN KOLONLARI YAKALAMAK KALDI. BU DURUMDA BİR KOLONUN UPDATE EDİLİP EDİLMEDİĞİNİ ANLAMAK
İÇİN YENİ VE ESKİ DEĞERLERİNİN KARŞILAŞTIRILMASI GEREKİYOR.
BUNUN İÇİN DE KOLON KOLON KARŞILAŞTIRMAK GEREKİYOR.
******************************************************/

--UPDATE İŞLEMİ İÇİN LOG TABLOMUZA İNSERT ATIYORUZ.
--BURADA @UNIONCOUNT>@INSERTCOUNT İSE EN AZ BİR KOLON GÜNCELLENMİŞTİR.
--AKABİNDE DEĞİŞEN KOLONLARI BULMAK İÇİN AŞAĞIDAKİ UZUN SQL CÜMLEMİZİ YAZIYORUZ.

IF @LOG_ACTIONTYPE='U' AND @UNIONCOUNT>@INSERTCOUNT
BEGIN  
	INSERT INTO CRM_LOG.DBO.CUSTOMER (
	ID,CUSTOMERNAME,CITY,DISTRICT,BIRTHDATE,GENDER, _LOG_DATE, _LOG_USERNAME, _LOG_HOSTNAME, _LOG_ACTIONTYPE, _LOG_PROGRAMNAME, _LOG_SQL,_LOG_UPDATEDCOLUMNS) 
	SELECT D.ID,D.CUSTOMERNAME,D.CITY,D.DISTRICT,D.BIRTHDATE,D.GENDER
	,GETDATE() AS _LOG_DATE,SUSER_NAME() AS _LOG_USERNAME,HOST_NAME() _LOG_HOSTNAME,@LOG_ACTIONTYPE _LOG_ACTIONTYPE,PROGRAM_NAME() _LOG_PROGRAMNAME,@LOG_SQL _LOG_SQL,CASE WHEN (I.ID<>D.ID) THEN   'ID,' ELSE '' END+CASE WHEN (I.CUSTOMERNAME<>D.CUSTOMERNAME) THEN   'CUSTOMERNAME,' ELSE '' END+CASE WHEN (I.CITY<>D.CITY) THEN   'CITY,' ELSE '' END+CASE WHEN (I.DISTRICT<>D.DISTRICT) THEN   'DISTRICT,' ELSE '' END+CASE WHEN (I.BIRTHDATE<>D.BIRTHDATE) THEN   'BIRTHDATE,' ELSE '' END+CASE WHEN (I.GENDER<>D.GENDER) THEN   'GENDER,' ELSE '' END
FROM DELETED D
LEFT JOIN INSERTED I 
ON I.ID=D.ID
END 


--DELETE İŞLEMİ İÇİN İŞİMİZ DAHA KOLAY. DOĞRUDAN LOG ATIYORUZ.

IF @LOG_ACTIONTYPE='D'  
BEGIN  
 
INSERT INTO CRM_LOG.DBO.CUSTOMER (
ID,CUSTOMERNAME,CITY,DISTRICT,BIRTHDATE,GENDER, _LOG_DATE, _LOG_USERNAME, _LOG_HOSTNAME, _LOG_ACTIONTYPE, _LOG_PROGRAMNAME, _LOG_SQL,_LOG_UPDATEDCOLUMNS) 
SELECT D.ID,D.CUSTOMERNAME,D.CITY,D.DISTRICT,D.BIRTHDATE,D.GENDER
,GETDATE() AS _LOG_DATE,SUSER_NAME() AS _LOG_USERNAME,HOST_NAME() _LOG_HOSTNAME,@LOG_ACTIONTYPE _LOG_ACTIONTYPE,PROGRAM_NAME() _LOG_PROGRAMNAME,@LOG_SQL _LOG_SQL,''
FROM DELETED D
END  
END

Gelin şimdi tablomuzu bir update edelim bakalım ne oluyor?

Şimdi de birden fazla alanı update edelim.

Şimdi aynı değerler ile tekrar update edelim bakalım trigger çalışacak mı?

Görüldüğü gibi değişiklik olmadığı için log atmadı.  

Şimdi içeriye bir kayıt daha ekleyip sonra kaydı silelim.

Son durum bu şekilde.

Şimdi Hakan kaydını silelim.

Görüldüğü gibi kayıt silindi ve log tablosuna silinen değerler atıldı.

Şimdi dilerseniz bir de SQL Injection örneği yapalım.

Örneğin basit bir öneri formu uygulaması ve SQL Injection açığı olmuş olsun.

Bunun için bir tablo oluşturalım.

CREATE TABLE [dbo].[ONERI](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ISIM] [varchar](100) NULL,
	[ONERI] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Basit bir asp.net uygulaması ile bu  tabloya öneri kaydı yapacağız. Bu arada SQL Injection saldırısı yapacağız ve customer tablosundan kayıt sileceğiz. ASP. Uygulamamızda connection string ile SQL

Server’a bağlanırken Application Name attribute’üne Client’ın ip adresini de ekleyeceğiz.

Öneri toplama formu bu şekilde. Buraya bir SQL Injection saldırısı yapalım.

Gönder tuşuna bastıktan sonra sistem öneri formuna bir satır atarken geri tarafta bizim Customer tablosunun içini uçuruyor.

Görüldüğü gibi trigger’ımız bizim istediğimiz her alanı yakalamış durumda.

Şimdi bu yapının iş gördüğüne kanaat getirdikten sonra gelelim bütün tablolar için bu işi otomatik olarak yapma işine.

Bunun için de iki tane procedure ümüz var. Birincisi Log tablosunu oluşturuyor, ikincisi ise asıl tablonun altına trigger’ı ekliyor.

Şimdi bu procedure’lerimize bakalım. Canlı database üzerinde bu procedure leri create edeceğiz.

İlk procedure ümüz tabloyu oluşturuyor.

CREATE PROC [dbo].[CREATE_LOG_TABLE]
 @TABLENAME AS VARCHAR(1000)  ='CUSTOMER'
AS

DECLARE @DBNAME AS VARCHAR(1000)=''

 
 
DECLARE @SQL AS NVARCHAR(MAX)=''
DECLARE @COLUMNS AS VARCHAR(MAX)=''

DECLARE @COLUMN_NAME AS VARCHAR(200)
DECLARE CRS CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TABLENAME
OPEN CRS
FETCH NEXT FROM CRS INTO @COLUMN_NAME
WHILE @@FETCH_STATUS=0
BEGIN
IF @COLUMN_NAME='LOGICALREF'
	SET @COLUMN_NAME='CONVERT(INT,0) AS LOGICALREF'

SET @COLUMNS=@COLUMNS+@COLUMN_NAME+','	
		
FETCH NEXT FROM CRS INTO @COLUMN_NAME
END
CLOSE CRS
DEALLOCATE CRS 


--SET @COLUMNS=SUBSTRING(@COLUMNS,1,LEN(@COLUMNS)-1)
SET @COLUMNS=@COLUMNS+'CONVERT(DATETIME,NULL) AS _LOG_DATE,'
SET @COLUMNS=@COLUMNS+'CONVERT(VARCHAR(500),NULL) AS _LOG_USERNAME,'
SET @COLUMNS=@COLUMNS+'CONVERT(VARCHAR(500),NULL) AS _LOG_HOSTNAME,'
SET @COLUMNS=@COLUMNS+'CONVERT(VARCHAR(10),NULL) AS _LOG_ACTIONTYPE,'
SET @COLUMNS=@COLUMNS+'CONVERT(VARCHAR(500),NULL) AS _LOG_PROGRAMNAME,'
SET @COLUMNS=@COLUMNS+'CONVERT(VARCHAR(MAX),NULL) AS _LOG_SQL,'
SET @COLUMNS=@COLUMNS+'CONVERT(VARCHAR(MAX),NULL) AS _LOG_UPDATEDCOLUMNS'

SELECT @COLUMNS 
SET @SQL='IF NOT EXISTS (SELECT * FROM '+DB_NAME()+'_LOG.DBO.SYSOBJECTS WHERE XTYPE=''U'' AND NAME='''+@TABLENAME+''') SELECT TOP 0 '+@COLUMNS+' INTO '+DB_NAME()+'_LOG.DBO.'+@TABLENAME+' FROM '+@DBNAME+'.DBO.'+@TABLENAME 
 SELECT @SQL 
 EXEC SP_EXECUTESQL @SQL 

Şimdi mevcut log tablomuzu silip bu procedure ile otomatik yeniden oluşturacağım.

Tablomuz artık gitti görüldüğü gibi.

Şimdi yenisini oluşturalım.

Şimdi sırada trigger’ı oluşturmak var. Onun için de aşağıdaki procedure’ü kullanacağız.

USE [CRM]
GO
/****** Object:  StoredProcedure [dbo].[CREATE_LOG_TRIGGER]    Script Date: 23.1.2020 17:30:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[CREATE_LOG_TRIGGER] 
@TABLENAME AS VARCHAR(200)='CUSTOMER'
AS
DECLARE @SQL AS NVARCHAR(MAX)
 
SET @SQL='IF EXISTS (SELECT * FROM sys.triggers where NAME=''TRG_'+@TABLENAME+'_LOG'')  DROP TRIGGER  TRG_'+@TABLENAME+'_LOG
' 
EXEC SP_EXECUTESQL @SQL 

DECLARE @PRIMARYKEYCOLUMN AS VARCHAR(255)
SELECT 
    @PRIMARYKEYCOLUMN=COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    JOIN 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu 
        ON tc.CONSTRAINT_NAME = ccu.Constraint_name
WHERE 
    tc.TABLE_NAME = @TABLENAME AND 
    tc.CONSTRAINT_TYPE = 'PRIMARY KEY'

SET @SQL='CREATE TRIGGER TRG_'+@TABLENAME+'_LOG ON '+@TABLENAME
SET @SQL=@SQL+'
AFTER UPDATE,DELETE'
SET @SQL=@SQL+'
AS'
SET @SQL=@SQL+'
BEGIN'
SET @SQL=@SQL+'
DECLARE @LOG_SQL AS VARCHAR(MAX)
DECLARE @LOG_ACTIONTYPE AS VARCHAR(20)
 
SET NOCOUNT ON;
DECLARE @INSERTCOUNT AS INT=0
DECLARE @DELETECOUNT AS INT=0
DECLARE @UNIONCOUNT AS INT=0

SELECT @INSERTCOUNT=COUNT(*) FROM INSERTED 
SELECT @DELETECOUNT=COUNT(*) FROM DELETED 
SELECT @UNIONCOUNT=COUNT(*) FROM
(SELECT * FROM INSERTED 
UNION 
SELECT * FROM DELETED) T'
SET @SQL=@SQL+'
IF @INSERTCOUNT>0 AND @DELETECOUNT>0
	SET @LOG_ACTIONTYPE=''U''
IF @INSERTCOUNT=0 AND @DELETECOUNT>0
	SET @LOG_ACTIONTYPE=''D''
IF @INSERTCOUNT>0 AND @DELETECOUNT=0
	SET @LOG_ACTIONTYPE=''I''
'
DECLARE @COLUMNS AS NVARCHAR(MAX)
DECLARE @COLUMNS2 AS NVARCHAR(MAX)
SELECT  @COLUMNS=(
  SELECT  
'D.'+COLUMN_NAME+','
 
  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TABLENAME  FOR XML PATH('')
)
 
  SET @COLUMNS=REPLACE(@COLUMNS,'&lt;','<')
  SET @COLUMNS=REPLACE(@COLUMNS,'&Gt;','>')
  SET @COLUMNS=SUBSTRING(@COLUMNS,1,LEN(@COLUMNS)-1)


 
SELECT  @COLUMNS2=(
  SELECT  
 'CASE WHEN (I.'+COLUMN_NAME+'<>D.'+COLUMN_NAME+') THEN   '''+COLUMN_NAME+','' ELSE '''' END'++'+'   
 
  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TABLENAME
  FOR XML PATH('')
)

  SET @COLUMNS2=REPLACE(@COLUMNS2,'&lt;','<')
  SET @COLUMNS2=REPLACE(@COLUMNS2,'&Gt;','>')
  SET @COLUMNS2=SUBSTRING(@COLUMNS2,1,LEN(@COLUMNS2)-1)
 
SET @SQL=@SQL+ 'CREATE TABLE #INPUTBUFFER (EVENTTYPE VARCHAR(200),PARAMETERS VARCHAR(1000),EVENTINFO VARCHAR(MAX))
INSERT INTO #INPUTBUFFER EXEC(''DBCC INPUTBUFFER(@@SPID)'')
SELECT @LOG_SQL=EVENTINFO FROM #INPUTBUFFER 
DROP TABLE #INPUTBUFFER
'

SET @SQL=@SQL+ '
IF @LOG_ACTIONTYPE=''U'' AND @UNIONCOUNT>@INSERTCOUNT
BEGIN  
DECLARE @LASTREF AS BIGINT
'
SET @SQL=@SQL+ 'INSERT INTO '+DB_NAME()+'_LOG.DBO.'+@TABLENAME+' (
'
SET @SQL=@SQL+REPLACE(@COLUMNS,'D.','')+', _LOG_DATE, _LOG_USERNAME, _LOG_HOSTNAME, _LOG_ACTIONTYPE, _LOG_PROGRAMNAME, _LOG_SQL,_LOG_UPDATEDCOLUMNS) 
'

SET @SQL=@SQL+ 'SELECT '+@COLUMNS

SET @SQL=@SQL+ '
,GETDATE() AS _LOG_DATE,SUSER_NAME() AS _LOG_USERNAME,HOST_NAME() _LOG_HOSTNAME,@LOG_ACTIONTYPE _LOG_ACTIONTYPE,PROGRAM_NAME() _LOG_PROGRAMNAME,@LOG_SQL _LOG_SQL,'+@COLUMNS2

SET @SQL=@SQL+ '
FROM DELETED D
LEFT JOIN INSERTED I 
ON I.'+@PRIMARYKEYCOLUMN+'=D.'+@PRIMARYKEYCOLUMN+'
END 
'

SET @SQL=@SQL+ '
IF @LOG_ACTIONTYPE=''D''  
BEGIN  

'
SET @SQL=@SQL+ 'INSERT INTO '+DB_NAME()+'_LOG.DBO.'+@TABLENAME+' (
'
SET @SQL=@SQL+REPLACE(@COLUMNS,'D.','')+', _LOG_DATE, _LOG_USERNAME, _LOG_HOSTNAME, _LOG_ACTIONTYPE, _LOG_PROGRAMNAME, _LOG_SQL,_LOG_UPDATEDCOLUMNS) 
'
SET @SQL=@SQL+ 'SELECT '+@COLUMNS

SET @SQL=@SQL+ '
,GETDATE() AS _LOG_DATE,SUSER_NAME() AS _LOG_USERNAME,HOST_NAME() _LOG_HOSTNAME,@LOG_ACTIONTYPE _LOG_ACTIONTYPE,PROGRAM_NAME() _LOG_PROGRAMNAME,@LOG_SQL _LOG_SQL,'''''

SET @SQL=@SQL+ '
FROM DELETED D
 
END 
'
--------------------INSERT----------------
SET @SQL=@SQL+ '
IF @LOG_ACTIONTYPE=''I''  
BEGIN  
'
SET @SQL=@SQL+ 'INSERT INTO '+DB_NAME()+'_LOG.DBO.'+@TABLENAME+' (
'
SET @SQL=@SQL+REPLACE(@COLUMNS,'D.','')+', _LOG_DATE, _LOG_USERNAME, _LOG_HOSTNAME, _LOG_ACTIONTYPE, _LOG_PROGRAMNAME, _LOG_SQL,_LOG_UPDATEDCOLUMNS) 
'
SET @SQL=@SQL+ 'SELECT '+@COLUMNS

SET @SQL=@SQL+ '
,GETDATE() AS _LOG_DATE,SUSER_NAME() AS _LOG_USERNAME,HOST_NAME() _LOG_HOSTNAME,@LOG_ACTIONTYPE _LOG_ACTIONTYPE,PROGRAM_NAME() _LOG_PROGRAMNAME,@LOG_SQL _LOG_SQL,'''''

SET @SQL=@SQL+ '
FROM INSERTED D
 
END 
'

SET @SQL=@SQL+'
END
'
SELECT @COLUMNS,@SQL ,@COLUMNS2
 EXEC SP_EXECUTESQL @SQL 
 

Trigger’ı oluşturmak için mevcut trigger’ı siliyoruz.

Şimdi procedure ile yeniden trigger’ı otomatik oluşturuyoruz.

Şimdi de tüm tablolar için bu işlemi nasıl yapacağız ona bakalım. Benim tavsiyem bu işi tüm tablolar için yapmak yerine kritik gördüğünüz tablolar için yapmanız. Zaten o yüzden procedure leri tablo bazında yaptım.

Önemli gördüğünüz tabloların sayısı da bir miktar olabileceğinden bu işi en kolay yapmanın yolu tablo isimlerini excel’e çekerek yapmak.

Bunun için şu sql kodunu kullanabilirsiniz. Tabi burada ilk önce loglanacak tabloları oluşturuyoruz.

SELECT 
'EXEC CRM.DBO.CREATE_LOG_TABLE '''+NAME+'''' AS SQL_,
NAME AS TABLENAME
 FROM SYSOBJECTS WHERE XTYPE='U' 

Bu sorgudan dönen cevap bu şekildedir.

Dönen sonucu Excel’e alıp içinden istediklerimizi temizleyebiliriz.

Excel’den de management Studio’ya yapıştırabiliriz.

Aşağıdaki işlem ile log tabloları oluşturuldu.

Aynı işlemi şimdi trigger için yapıyoruz.

SELECT 
'EXEC CRM.DBO.CREATE_LOG_TRIGGER '''+NAME+'''' AS SQL_,
NAME AS TABLENAME
 FROM SYSOBJECTS WHERE XTYPE='U' 

Peki sistemimizde iki tablo değil de çok daha fazla tablo olsaydı. Örneğin bir Logo veritabanı olsun.

350 Tablo için log tablolarını otomatik oluşturacağız.

Tablolar oluştu.

Şimdi de trigger’ları oluşturalım.

Aşağıdaki gibi trigger oluştu.

Şimdi bu tabloda bir kayıt update edelim.

Bu da loglanan kaydın bilgisi.

Şimdi de bir kayıt silelim.

Görüldüğü gibi otomatik olarak tüm işimizi gören bir yapı oluşturduk. Burada örnek olsun diye update, delete işlemlerini Management Studio üzerinden yaptım. Başka uygulamalardan da örneğin Logo Tiger’ın kendi içinden yapsak da aynı şekilde yakalayabiliriz.

Uzun bir makale oldu. Buraya kadar geldiyseniz sabırla okuduğunuz  için teşekkür ederim.

Sağlıcakla…