Daha önce “DDL Trigger nedir? Nasıl Kullanılır?” konulu bir makale yazmıştır. Hatta bu makalemde DDL Trigger ile login lerin nasıl yönetileceği üzerinde durmuştuk. Okumadıysanız bu yazımı okumanızı tavsiye ederim.
http://www.turgaysahtiyan.com/post/SQL-Server-e28093-Logon-DDL-Trigger-ile-Login-leri-Yonetme.aspx
Bugünkü yazımda ise; DDL Trigger ile DDL Event lerin nasıl loglanabileceğini işliyor olacağız.
Bu yapı ile büyük ölçekli firmalarda sizin dışınızda gelişen DDL Eventleri loglayıp bakabileceksiniz.
Örneğin Developer larınız hangi SP leri değiştirmiş, değiştirmeden önceki hali neymiş, yeni hangi loginler create edilmiş gibi DDL Event adı altında sayılan bütün işlemleri loglayacağız.
Bunun için AdventureWorks DB sinde verilen örneği kullanacağım.
İlk önce bir örnek bir DB create edip daha sonrada logları yazacağımız bir table create edelim.
CREATE DATABASE dbDDLEventLog
GO
USE [dbDDLEventLog]
GO
CREATE TABLE [dbo].[DatabaseLog](
[DatabaseLogID] [int] IDENTITY(1,1) NOT NULL,
[PostTime] [datetime] NOT NULL,
[DatabaseUser] [sysname] NOT NULL,
[Event] [sysname] NOT NULL,
[Schema] [sysname] NULL,
[Object] [sysname] NULL,
[TSQL] [nvarchar](max) NOT NULL,
[XmlEvent] [xml] NOT NULL,
CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED
(
[DatabaseLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Şimdi loglamayı yapacak DDL Trigger ı create edelim. Açıklamalar kod un içerisindedir.
USE [dbDDLEventLog]
GO
CREATE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS AS
BEGIN
SET NOCOUNT ON;
DECLARE @data XML;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @eventType sysname;
--DDL Event i alıyoruz. Daha sonra DDL Event teki bilgileri değişkenleri alacağız.
SET @data = EVENTDATA();
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
IF @object IS NOT NULL
PRINT ' ' + @eventType + ' - ' + @schema + '.' + @object;
ELSE
PRINT ' ' + @eventType + ' - ' + @schema;
IF @eventType IS NULL
PRINT CONVERT(nvarchar(max), @data);
--Log ları tuttuğumuz tabloya DDL Event i yazıyoruz.
INSERT [dbo].[DatabaseLog]
(
[PostTime],
[DatabaseUser],
[Event],
[Schema],
[Object],
[TSQL],
[XmlEvent]
)
VALUES
(
GETDATE(),
CONVERT(sysname, CURRENT_USER),
@eventType,
CONVERT(sysname, @schema),
CONVERT(sysname, @object),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
@data
);
END;
GO
Şimdi Trigger ı tetikleyecek bir işlem yapalım. Örneğin yeni bir table create edelim ve bunun loglandığını izleyelim.
USE [dbDDLEventLog]
GO
CREATE table ddlDeneme (a int, b varchar(10))
GO
Log ların tutulduğu tabloya bakalım bu event gelmiş mi?
USE [dbDDLEventLog]
GO
select * from DatabaseLog
GO
Gördüğünüz gibi table create işlemi loglandı. Sonuca bakacak olursak TSQL kolonunda işlemin TSQL komutu bulunmakta. XMLEVent kolonunda ki veriye tıklarsak yeni bir ekranda yapılan işlemin xml kodunu dökmekte.
<EVENT_INSTANCE>
<EventType>CREATE_TABLE</EventType>
<PostTime>2010-06-13T22:06:26.080</PostTime>
<SPID>55</SPID>
<ServerName>FUNNYCIK\S10ENT</ServerName>
<LoginName>funnycik\tugi</LoginName>
<UserName>dbo</UserName>
<DatabaseName>dbDDLEventLog</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>ddlDeneme</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>CREATE table ddlDeneme (a int, b varchar(10))
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
Bu xml kod da işlemle alakalı bütün detaylı bilgiyi bulabilirsiniz.
Table sonucu gelen veri için kolon açıklamalarını da yazıp yazımızı noktalayalım.
| DatabaseLogID |
Table için primary key |
| PostTime |
İşlemin yapıldığı zaman |
| DatabaseUser |
İşlemi yapan user |
| Event |
İşlem Tipi (DDL Event Type) |
| Schema |
İşlemin hangi schema da yapıldığı |
| Object |
İşlemin hangi obje üzerinde yapıldığı |
| TSQL |
işlemin TSQL kodu |
| XmlEvent |
İşlemin XML Event i |
İyi çalışmalar
Turgay Sahtiyan
4dba951b-e446-4321-8c94-2a21761de8cb|0|.0