SQL’de Silinen ve Güncellenen Dataları Log’lama
Herkese merhaba, bugün SQL veri tabanı üzerinde tutulan bir tablomuzdan silinen ve güncellenen dataların loğlarını başka bir tablo oluşturarak orada tutalım. Kim ne zaman hangi makine ve hangi sorgu ile veri tabanımızda ki verileri değiştirmiş görebilelim.
Bunu yaparken SQL'de triggerlardan yararlanacağız. Öncelikle herhangi bir veri tabanı oluşturup örnek bir tablo create edelim ve bu tabloya örnek veriler girelim. Daha sonra ayrı bir log tablosu oluşturup biraz önce oluşturduğumuz örnek tablonun sütunlarını bu tablomuza da verelim.
Ben bu örneğimde örnek tablo olarak “Categories” loglama için ise “Logs” tablolarımı oluşturdum. Logs tablomda ayrıca dataların silinip güncellendiğinde kimler tarafından ne zaman değiştirildiği bilgilerini tutmak için ise ekstra sütunlar (LOG_HOSTNAME, LOG_DATE, LOG_PROGRAM, LOG_USERNAME, LOG_SQL, LOG_ACTIONTYPE) ekliyorum.
Artık trigger yazma vakti geldiğine göre sql management studio üzerinden “New Query” alanından yeni sorgu sayfası açıp trigger sorgumu yazmaya başlıyorum.
Öncelikle yukarıda kullanıcının bilgilerini alacağımız fonksiyonlarımızı açıklamakla başlayalım.
LOG_HOSTNAME: HOST_NAME()
LOG_DATE: GETDATE()
LOG_PROGRAM: PROGRAM_NAME()
Yukarıdaki hazır fonksiyonlar Sql'in bize sunmuş olduğu fonksiyonlardır. Yani Host Name almak istiyorsam yukarıda ki HOST_NAME() fonksiyonu nu çalıştırmam yeterli. Bu fonksiyonların nasıl çıktı ürettiklerini merak edenler fonksiyon başına Select koyarak çalıştırıp görebilirler. (Örneğin Select HOST_NAME())
Daha sonra LOG_ACTIONTYPE alanıma güncelleme işlemi ise Update silme işlemi için ise Delete yazdırmam için dinamik olarak sql ‘de bir değişken tanımlayıp bu değişkenime if sorgusu ile yazdırabilirim. Peki ama nasıl? Şu şekilde;
Evet gördüğünüz gibi sql’de tablo üzerinde silme veya güncelleme işlemleri için gelen sorguları bu şekilde if sorgusu ile kolayca yakalayabiliyorum.
Artık son bir alanımız kaldı LOG_SQL sütunu alanına kullanıcı nasıl sorgu göndermiş bunu yakalayıp yazdırmamız gerekli. Bunun için Sql yine yardımımıza koşarak hangi sql sorgusunun çalıştırıldığını bize veren bir sorguyu hali hazırda veriyor. DBCC INPUTBUFFER(57). Peki burada ki 57 de neyin nesi der gibisiniz.
Sql management studio açtığımızda program bize otomatik olarak bir oturum kimliği atar işte burada yazdığımız 57 benim oturum Id(kimliğim) olmaktadır. Peki her oturumu açtığımda aynı Id ’mi atanıyor hayır o zaman bizim bu Id ‘yi dinamik olarak almamız gerekiyor işte burada da Sql bize tam senin ihtiyacın olan sorgun bende diyor ve “@@SPID” komutunu veriyor. Tahmin ettiğiniz üzere bu komutum ise benim oturum Id ’mi vermektedir.
Şimdi elimizde “DBCC INPUTBUFFER(@@SPID)” bu fonksiyonumuz var fakat biz bu fonksiyonu çalıştırdığımızda şu görüntüyle karşılaşıyoruz.
EventInfo bölümü bizim işimize yarayacak alan evet tamda düşündüğünüz gibi. O zaman sql runtime zamanında triggerım çalışırken yani bir tablo oluşturacağız bu tabloya kullanıcının gönderdiği sorguyla kaydedip bu tabloya kaydedilen sorgunun ise EvenInfo sütununda yazan alanı alıp değişkenimize atacağız ve oluşturduğumuz (run time anında ki) tabloyu drop edeceğiz.
Nasıl mı ? Bu şekilde
CREATE TABLE #T (EVENTTYPE_ VARCHAR(100), PARAMETERS_ VARCHAR(100), EVENTINFO_ VARCHAR(MAX))
DECLARE @SQL AS NVARCHAR(MAX)
SET @SQL = ‘INSERT INTO #T EXEC (‘’ DBCC INPUTBUFFER(‘+ CONVERT(VARCHAR, @@SPID)+’) ‘’)’
EXEC SP_EXECUTESQL @SQL
SELECT @LOG_SQL = EVENTINFO_ FROM #T
DROP TABLE #T
Evet artık @LOG_SQL değişkenimde elde ettiğim değerim kullanıcının tablomuza gönderdiği sql sorgusu yani bu değişkeni log tablomuzda ki LOG_SQL sütunu alanına denk gelen yere ekliyoruz ve artık sorgumuz tamamdır.
Artık Categories tabloma birisi sorgu ile update yada delete gönderirse triggerimin bunu yakalayıp Logs tabloma kaydetmesini istiyorum o halde gelin deneyelim.
Categories tablomda yer alan örnek bir sutunu güncelleyecek sorgumu yazıp çalıştırdım. Artık Log tablomda bu kaydı kim ne zaman değiştirmiş gibi bilgileri görmem gerekiyor.
ve gördüğünüz gibi triggerim aslanlar gibi çalışıp sorgumu yakalayıp logs tabloma kaydını girmiş. Aynı şekilde delete sorgusu göndersem onu da yakalayıp buraya kaydeder hemen onu da test edelim.
Evet artık Delete ve Update sorgularımı yakalayıp Logs tabloma kaydeden triggerim çalıştığını test ettik. Önemli datalarımızın tutulduğu tablolarda kimler ne değişiklik yapmış bunları izlememiz için gerekli olan triggerımızı yazdık. Umarım size de faydalı olmuştur. Yazdığımız Sql trigger sorgusunu aşağıya hem görselini hemde metin olarak kodlarını bırakıyor olacağım yararlanmak isteyen olursa alıp sorguyu kendine göre özelleştirip kullanabilir.
USE [ETest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[LOG_TRIGGER] ON [dbo].[Categories]
AFTER UPDATE, DELETE
AS
BEGIN
DECLARE @LOG_HOSTNAME AS VARCHAR(255) = HOST_NAME()
DECLARE @LOG_DATE AS DATETIME = GETDATE()
DECLARE @LOG_PROGRAM AS VARCHAR(255) = PROGRAM_NAME()
DECLARE @LOG_USERNAME AS VARCHAR(255) = SUSER_NAME()
DECLARE @LOG_SQL AS VARCHAR(MAX)
DECLARE @LOG_ACTIONTYPE AS VARCHAR(15)
IF EXISTS (SELECT * FROM DELETED)
SET @LOG_ACTIONTYPE = ‘DELETE’
IF EXISTS (SELECT * FROM INSERTED)
SET @LOG_ACTIONTYPE = ‘UPDATE’
SELECT GETDATE()
DBCC INPUTBUFFER(@@SPID) — HANGİ SQL CÜMLESİNİ ÇALIŞTIRDI. BU BİR PROSEDUR
CREATE TABLE #T (EVENTTYPE_ VARCHAR(100), PARAMETERS_ VARCHAR(100), EVENTINFO_ VARCHAR(MAX))
DECLARE @SQL AS NVARCHAR(MAX) — NVARCHAR YADA VARCHAR TİPİNDE OLMAK ZORUNDA
SET @SQL = ‘INSERT INTO #T EXEC (‘’ DBCC INPUTBUFFER(‘+ CONVERT(VARCHAR, @@SPID)+’) ‘’)’
EXEC SP_EXECUTESQL @SQL
SELECT @LOG_SQL = EVENTINFO_ FROM #T
DROP TABLE #T
INSERT INTO Logs
(Id ,[Name], CreatedOn, [Description], LOG_HOSTNAME, LOG_DATE, LOG_PROGRAM, LOG_USERNAME, LOG_SQL, LOG_ACTIONTYPE)
SELECT
Id, [Name], CreatedOn,[Description], @LOG_HOSTNAME, @LOG_DATE, @LOG_PROGRAM, @LOG_USERNAME, @LOG_SQL, @LOG_ACTIONTYPE
FROM DELETED
END
Geldik bir makalenin daha sonuna. Yeni bir makalede görüşmek üzere hoşçakalın.