SQL Server Tablo Değişikliklerini Dinamik Olarak Kayıt Altına Alma
Merhabalar,
SQL’de olması gereken en büyük ihtiyaçlardan biri olan tablo değişikliklerini kayıt altına alma hakkında örnekler ile anlatıyor olacağım.
Başlamdan önce yapıdan kısaca bahsetmem gerekirse: Oldukça basit bir şekilde işlemekte olup, ilgili tabloda yapılacak insert,update ve delete işlemlerinde sadece etkilenen alanların önceki ve güncel değerlerini bir tabloda saklıyor olacağız.
Kod mantığını da özetlemek gerekir ise, loglama yapmak istediğimiz tablolarda şablon niteliğinde trigger yazıyor olacağız, daha sonra global bir stored procedure ile birlikte iş mantığımızı gerçekleştirip log tablolarımızda kayıtları saklıyor olacağız.
Kullanacağımız Nesneler
1 — Test tablomuz (insert,update ve delete işlemleri için)
2 — Değişiklikleri kayıt altına alacağımız 2 adet tablomuz. (Ana bilgiler için ve değişiklik yapılan alanları içeren tablolarımız)
3 — İş mantığında dinamik bir sorgu yazmamız gerektiği için veri tiplerini uygun bir şekilde dönüştürecek yapıyı bize sunacak bir adet fonksiyon.
4 — İş mekanizmasını kurgulayacağımız global bir stored procedure.
5 — Tüm bu iş akış sürecinin çalışmasını sağlayacak trigger kodlarımız.
1 — Test Tablosu
2 — Log Tabloları
Burada 2 tablo kullanmamızın sebebi ise:
- ChangeLog’ta ilgili tablo ve eylem hakkında ana bilgilerimizi tutmamız ve hızlı filtreleme yapabiliyor olmamızdır.
- ChangeLogRawData’da alan ile ilgili özet bilgiler, önceki değer ve güncel değer bilgilerini bire çok bir mantıksal ilişkide saklayacağımız içindir.
3 — Çevirici Fonksiyon
Bu fonksiyonun amacı ise, iş mekanizmasını kurgulayacağımız stored procedure için dinamik sorgumuzda kod tekrarına gitmemek ve unpivot içerisinde aynı veri tipiyle kullanabilmek amacıyla kullandığımızı düşünebiliriz.
4 — Dinamik Loglama Mekanizması Prosedürü
Yapıdan kısaca bahsetmek gerekirse:
Trigger içerisinden bize gelecek “UPDATED COLUMNS”, “INSERTED” ve “UPDATED” değerlerinin bir temp tabloya atılması ile birlikte işlemlerimize burada gerçekleştiriyor ediyor olacağız.
Procedure gelen parametreleri ele aldığımız ise:
- @pObjectID (Tablonun sys.tables tablosunda ki “unique id” si)
- @pObjectName (Tablonun sys.tables tablosunda ki name kolonu yani ismi)
- @pSchemaID (Tablonun sys.tables tablosunda ki “schema id” si)
- @pSchemaName (Tablonun sys.schema tablosunda ki “name” kolonu yani ismi.)
- @pID (ilgili tabloda ki ID kolonunun ismi.)
- @pActionType (Yapılan eylemin türü, 1,2,3 olarak gelir insert,update,delete işlemi olduğunu belirtir.)
olarak düşünebiliriz.
Dinamik sorgudan bahsetmek gerekirse:
Dinamik sorgunun amacı aslında şudur, trigger içerisinde yaptığımız etkilenen kolonlar hesaplaması ile birlikte #vColumn isimli geçici tablosunda kolon bilgilerini saklıyoruz. (Verileri değil, kolon özelliklerini) Bu bilgiler dikey bir formatta saklanmakta. Ancak yine trigger içerisinden gelen #vInserted ve #vDeleted geçici tablolarında ise veriler yatay bir formatta saklanmaktadır. #vInserted ve #vDeleted için unpivot yöntemiyle yatayda saklanan verileri dikey bir formata çevirmemiz gerekmekte. Bu bu geçici tablolardan ise sorgulama sürecine sadece etkilenen kolonları dahil etmeliyiz, bu sebeple dinamik bir sorgu hazırlıyoruz.
Dinamik sorguda ki where koşullarımız:
- İlk koşulumuz olan i.InsertedValue ve d.DeletedValue eşitsizliği koşulumuz ilgili alan update edilmiş olsa bile eğer içerisinde ki değer değiştirilmediyse sorgumuzdan elemiş oluyoruz.
- İkinci koşulumuzda ise tablomuzda eğer ModifyUserID ve ModifyDate gibi audit kolonları kullanıyor isek, bu kolonlarda veriler güncellenmese dahi kayıt altında tutmak istediğimizi belirtiyoruz.
ChangeLog ve ChangeLogRawData tablolarına kayıt oluşturmak:
Bu kısımda ise artık dinamik sorgumuzdan oluşan @vColumnChanges tablo tipi değişkenimizde tuttuğumuz verileri gerek bulk gerek single transactional işlemler için tablolarımıza ekleme işlemini gerçekleştiriyoruz ve iş mantığımız burada son bulmuş oluyor.
Aslında burayla ilgili bir çok geliştirme yapılabilir, çok daha güzel bir hale getirebilir. Eğer böyle bir şey yaparsanız paylaşıp bana da haber vermeyi unutmazsanız sevinirim :)
5 — Değişiklikleri Yakalayacak Trigger
Gelelim olayların başlangıç noktası olan kayıt altında tutmak istediğimiz tablolara ekleyeceğimiz trigger kodlarına:
Burada ki mantığımız aslında çok basit, özelleştirilebilir bir ortak şablon olarak düşünebiliriz. Amaç tamamen yapılan eylemin türü (update, insert ve delete gibi), işlem sırasında etkilenen kolonları tespit etmek, güncel ve eski değerlerin bir geçici tablo aracılığıyla çağıracağımız stored procedure üzerinde kullanılmasını sağlamaktır.
Trigger içerisinde ise, size bahsetmek istediğim olan en önemli kısım sadece etkilenen kolonların tespitidir. Bunun için SQL yardımımıza “COLUMNS_UPDATED()” fonksiyonu ile koşmaktadır. Bu fonksiyon scalar bir fonksiyon olmak ile birlikte etkilenen kolonları içerisinde saklayan binary türünde veri saklayan bir değerdir. Peki bunun için hangi kolonların etkilendiğini nasıl anlarız sorusunun cevabı ise bit tabanında kontrollerimizi gerçekleştirmemizi sağlayan bitwise operatörler mevcuttur. Bu operatörler arasından ise (& AND) olanı kullanıyor olacağız.
COLUMNS_UPDATED içerisinde arama yapmak içinse, pozisyon tabanlı bir içeriyor mu kontrolü yapmamız gerekmektedir. Bunun içinse sys.columns bizim için en gerekli sistem tablosudur. İlgili tablonun kolonlarını bize pozisyonu(column_id) ile birlikte listeler. Ancak bu pozisyonlar sys.columns’ta 1 den başlayarak ilerler ama bit tabanlı pozisyon kontrolleri için 0'dan başlamamız gerekmektedir. Bu yüzden column_id için -1 değerini kullanarak 0'dan başlamasını sağlamamız gerekiyor. Daha sonrasında ise kontrol etmemiz gereken en büyük kısım bir byte 8 bit saklayabileceği için, her ilgili column_id’den pozisyon bulurken kaçıncı byte üzerinde arama yapmamız gerektiğini belirtmemiz gerekecek. Bunun içinde basitçe (column_id -1) / 8 + 1 formülünü kullanabiliriz. SUBSTIRNG yardımı ile de binary türünde bir değer için hangi byte ulaşmak istediğimizi belirtebiliriz. Artık ilgili kontrol için hazır olduğumuza göre yapmamız gereken tek işlem ilgili kolonun güncellenip güncellenmediğini anlamak için hangi byte üzerinde arama yapacağımıza karar verip, bitwise AND operatörü ile ilgili byte’ta kolonun varlığını tespit etmek için kolon pozisyonunun 2. kuvvetini alarak kontrol sağlayabiliriz, bunun formülü ise POWER(2, (column_id — 1) % 8) olarak düşünebiliriz.
Hızlıca yukarda bahsettiğim adımlar için test amaçlı bir trigger yazacak ve test edecek olursak sonuç ne olurdu ? :
Triggeri oluşturduktan sonra hızlıca bir update komutu çalıştıralım ve sonuca birlikte bakalım :)
Sonuca baktığımızda gayet güzel bir şekilde sonuçları görüntüleyebiliyoruz. COLUMNS_UPDATED için ilgili kolon için kaçıncı byte gitmemiz gerektiğini öğreniyor ve gidiyoruz, sonrasında ise Bitwise AND operatörü ile birlikte kolon pozisyonunun 2. kuvvetini alıp kontrol sağlıyoruz. Eğer Bitwise AND sonucunda bize kolonun 2. kuvvetini dönüyor ise, bu kolonun COLUMNS_UPDATED içerisinde yer aldığını öğreniyor ve güncellenen bir kolon olduğunu tespit edebiliyoruz.
Umarım açıklayıcı ve öğretici olmuştur :) Fazla uzatmadan sistemin çalışmasını sağlayacak trigger yapısını da paylaşayım :)
5 — Değişiklikleri Yakalayacak Trigger Yapısı
Evet artık gerekli olan her şeyi oluşturduk. Artık kayıt ekleme, güncelleme ve silme sonrasında nasıl bir sonuç bizi bekliyor görebiliriz :)
Kayıt Ekleme İşlemi
Örnek kayıt ekleme ve log tabloları için sorgulama kodlarımız :
Ve sonuçlar
ChangeLog tablosu :
ChangeLogRawData tablosu :
Kayıt Güncelleme İşlemi
Örnek kayıt güncelleme ve log tabloları için sorgulama kodlarımız :
ChangeLog tablosu :
ChangeLogRawData tablosu:
Kayıt Silme İşlemi
Örnek kayıt silme ve log tabloları için sorgulama kodlarımız :
ChangeLog tablosu :
ChangeLogRawData tablosu:
