Syntax :
DBCC INPUTBUFFER (session_id [ , request_id ])
[WITH NO_INFOMSGS ]
Parametreleri :
session_id
Son aktif connection'daki oturumun id'sidir.
@@SPID global değişkeni ile, sql server'daki son request'in id si ni alabilirsiniz.
SELECT @@SPID
kodunu çalıştırarak örneğini görebilirsiniz.
request_id
Aktif oturumdaki, son istedğin id sidir.
Son gelen isteğin id sini aşağıdaki kodu kullanarak alabilirsiniz.
SELECT request_id
FROM sys.dm_exec_requests
WHERE session_id = @@spid
Aşağıdaki kod ise, son request ile ilgili daha fazla detay verir. Durumu ne, hangi veri tabanına gelmiş, kim çalıştırmış, ne kadar süre beklemiş gibi.
SELECT *
FROM sys.dm_exec_requests
WHERE session_id = @@spid
WITH
With, diğer DDL komutlarında olduğu gibi bir özellik vereceğimizi ifade eder.
NO_INFOMSGS
Hata seviyesi 0-10 arasında olan yani server tarafında bilgi mesajı olarak kabul edilen seviyede bir mesaj gelirse, bunu gizler.
Bu kadar teorik bilgiden sonra, "DBCC INPUTBUFFER" ın ne işimize yarayacağına ve nasıl kullanılacağımıza bakalım.
CDC kullanmadan, bir tablodaki değişiklikleri log'luyor isek, bu değişikliğin hangi sql cümlesi kullanılarak (Bu bir update/insert/delete statement olabileceği gibi, bir stored procedure de olabilir) yapıldığını saklayabiliriz.
Veya bir tablo, nedenini bulamadığımız bir şekilde güncelleniyor ise, bu güncelleme işleminin hangi kod tarafından yapıldığını yakalayabiliriz.
Aşağıdaki örnek SQL cümlesi, testCustomer isimli bir tablodaki her değişikliği loglayıp, değişiklikleri de testCustomer_Log isimli bir dosya da saklayacağız.
Önce kodlarımızı hazırlayalım.
CREATE TABLE testCustomer(CustomerID int identity(1,1),
FirstName varchar(100),
LastName varchar(100))
GO
--DROP TABLE testCustomer_Log
CREATE TABLE testCustomer_Log(ID int identity(1,1),
CustomerID int,
OldFirstName nvarchar(100),
OldLastName nvarchar(100),
NewFirstName nvarchar(100),
NewLastName nvarchar(100),
SqlCommand nvarchar(4000),
ChangedOn datetime default(GetDate())
)
GO
CREATE TRIGGER trTestCustomer ON testCustomer
AFTER UPDATE,INSERT,DELETE
AS BEGIN
DECLARE @InputBuffer TABLE(EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(4000))
INSERT INTO @InputBuffer
exec ('DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS')
DECLARE @SQLCommand nvarchar(4000)
SELECT @SQLCommand = EventInfo
FROM @InputBuffer
INSERT INTO testCustomer_Log(CustomerID,OldFirstName,OldLastName,
NewFirstName,NewLastName,SqlCommand)
SELECT isNull(i.CustomerID,d.CustomerID),d.FirstName,d.LastName,
i.FirstName,i.LastName,@SQLCommand
FROM inserted i
FULL JOIN deleted d on d.CustomerID = i.CustomerID
END
GO
CREATE PROCEDURE spTEST_UpdateCustomerName @FirstName nvarchar(100),@CustomerID int
AS BEGIN
UPDATE testCustomer SET FirstName = @FirstName WHERE CustomerID = @CustomerID
END
Data ve Log tablolarını yarattıktan sonra, Data tablomuz olan testCustomer üzerine bir trigger yazıyoruz. Bu trigger de @@SPID ile son oturumun id sini alıp, DBCC INPUTBUFFER a veriyoruz. Bize uyarı mesajları vermesin diye de mesajları "WITH NO_INFOMSG" yazarak kapatıyoruz.
DBCC INPUTBUFFER 3 kolondan, 1 satırdan oluşan bir tablo döndürür. Bu tablodan dönen değerleri @InputBuffer isimli bir değişken tabloya yazıyoruz ki, sonra DML kodunu çalıştıran kodu alabilelim.
EventInfo kolonunda, nvarchar(4000) tipinde bir bilgi gelir. İşte datayı değiştiren kod da bu kolonda bulunur. Yani çalışan script eğer 4000 karakterden fazla ise, ilk 4000 karakterini görebiliriz. Bu kodu @SQLCommand değişkenine atıyoruz.
Sonra değişen bilgilerin eski ve yeni bilgilerini yazabilmek için de, trigger içerisinde kullanılan inserted ve deleted tablolarını full join ile bağlıyoruz.
Unutmamak gerekir ki, bir tabloya insert kodu ile bir kayıt ekliyorsak, sadece inserted tablosunda kayıtlar olur, deleted tablosundan birşey gelmez. Aynı mantıkla, delete ile kayıt siliyorsak, sadece deleted tablosunda silinen kaydın bilgisi olur, inserted tablosunda birşey olmaz. Update ile tabloyu güncelliyor isek, yeni bilgiler inserted tablosunda, eski bilgiler de deleted tablosunda bulunur.
Birkaç örnek ile trigger ın log tablosuna neler yazdığını bulalım.
INSERT INTO testCustomer(FirstName,LastName) VALUES('Abc','DEF')
INSERT INTO testCustomer(FirstName,LastName) VALUES('Abc','fsfdsfdsf')
GO
INSERT INTO testCustomer(FirstName,LastName) VALUES('TestFN','TestLN')
GO
INSERT INTO testCustomer(FirstName,LastName) VALUES('TestFN2','TestLN2')
GO
UPDATE testCustomer SET FirstName = 'Abc Updated' WHERE FirstName = 'Abc'
GO
DELETE FROM testCustomer WHERE FirstName='TestFN'
GO
DECLARE @CustomerID int
SELECT @CustomerID = CustomerID
FROM testCustomer
WHERE FirstName='TestFN2'
exec spTEST_UpdateCustomerName @CustomerID = @CustomerID, @FirstName = 'TestFN3'
GO
SELECT * FROM testCustomer
SELECT * FROM testCustomer_Log
İşte Customer tablosunun son hali. 4 kayıt ekledik. Kayıtlardan birini update cümlesi ile güncelledik. Sonra başka bir kayıt sildik. Daha sonra da adı TestFN2 olan kaydın adını yazdığımız stored procedure ile güncelledik.
| CustomerID | FirstName | LastName |
| 1 | Abc Updated | DEF |
| 2 | Abc Updated | fsfdsfdsf |
| 4 | TestFN3 | TestLN2 |
| ID | CustomerID | OldFirstName | OldLastName | NewFirstName | NewLastName | SqlCommand | ChangedOn |
| 1 | 1 | Abc | DEF | INSERT INTO testCustomer(FirstName,LastName) VALUES('Abc','DEF') INSERT INTO testCustomer(FirstName,LastName) VALUES('Abc','fsfdsfdsf') | 13.10.2010 | ||
| 2 | 2 | Abc | fsfdsfdsf | INSERT INTO testCustomer(FirstName,LastName) VALUES('Abc','DEF') INSERT INTO testCustomer(FirstName,LastName) VALUES('Abc','fsfdsfdsf') | 13.10.2010 | ||
| 3 | 3 | TestFN | TestLN | INSERT INTO testCustomer(FirstName,LastName) VALUES('TestFN','TestLN') | 13.10.2010 | ||
| 4 | 4 | TestFN2 | TestLN2 | INSERT INTO testCustomer(FirstName,LastName) VALUES('TestFN2','TestLN2') | 13.10.2010 | ||
| 5 | 2 | Abc | fsfdsfdsf | Abc Updated | fsfdsfdsf | UPDATE testCustomer SET FirstName = 'Abc Updated' WHERE FirstName = 'Abc' | 13.10.2010 |
| 6 | 1 | Abc | DEF | Abc Updated | DEF | UPDATE testCustomer SET FirstName = 'Abc Updated' WHERE FirstName = 'Abc' | 13.10.2010 |
| 7 | 3 | TestFN | TestLN | DELETE FROM testCustomer WHERE FirstName='TestFN' | 13.10.2010 | ||
| 8 | 4 | TestFN2 | TestLN2 | TestFN3 | TestLN2 | DECLARE @CustomerID int SELECT @CustomerID = CustomerID FROM testCustomer WHERE FirstName='TestFN2' exec spTEST_UpdateCustomerName @CustomerID = @CustomerID, @FirstName = 'TestFN3' | 13.10.2010 |
İşte tüm yaptığımız değişikliker burada görünüyor.
İlk başta çalıştırdığımız insert cümlesi 2 kayıt birden insert edecek bir batch şeklinde yazılmıştı. Customer 1 ve Customer 2 kayıtlarında bu her iki cümlenin de yazdığını görebiliyoruz.
En ilginci de, 4 nolu CustomerID nin update cümlesi. Burada ilgili müşteri numarasını bulduktan sonra bunu bir procedure e veriyor ve onun güncelleme yapmasını bekliyorduk. Yazdığımız tüm kod da burada aynen görünebiliyor.
Peki testCustomer tablosunu TRUNCATE TABLE ile temizlersek ne olur? Log tablosuna hiç bir şey gelmez. Çünkü truncate kodu herhangi bir trigger'ı tetiklemez. Biz de işlerimizi trigger ile yaptığımız için log tablosunun herhangi birşeyden haberi olmaz.
Bir sonraki post'da DBCC INPUTBUFFER a benzer iş yapan fn_get_sql sistem fonksiyonundan ve ikisi arasındaki farklardan bahsedeceğiz.
