SQL Server - DDL 트리거 이용하여 테이블의 변화 감지 Audit Table changes in SQL Server

CREATE TRIGGER tr_AuditTableChanges
ON ALL SERVER
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
	Select EventData()
END

이렇게 하면 XML포맷의 Event_instance가 날라온다. 

<EVENT_INSTANCE>
  <EventType>CREATE_TABLE</EventType>
  <PostTime>2020-12-11T15:27:58.700</PostTime>
  <SPID>79</SPID>
  <ServerName>LAPTOP-SKVGRGJD\MSSQLSERVER01</ServerName>
  <LoginName>LAPTOP-SKVGRGJD\Home</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>sampleDB</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>TableChanges</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>Create table Tablechanges
(
DatabaseName nvarchar(250),
TableName nvarchar(250),
EventType nvarchar(250),
LoginName nvarchar(250),
SQLCommand nvarchar(2500),
AuditDateTime datetime
)</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

 

 

데이터베이스에 트리거로 오는 XML를 분석하여 저장하려면 아래와 같이 TRIGGER를 수정하거나 만든다.

CREATE TRIGGER tr_AuditTableChanges
ON ALL SERVER
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
 DECLARE @EventData XML
 SELECT @EventData = EVENTDATA()

 INSERT INTO SampleDB.dbo.TableChanges 
 (DatabaseName, TableName, EventType, LoginName, SQLCommand, AuditDateTime)
 VALUES
 (
  @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(250)'),
  @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(250)'),
  @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(250)'),
  @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(250)'),
  @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2500)'),
  GetDate()
 ) 
END

 이렇게 한다음에 테이블을 생성 수정 삭제를 하면 

설정한 TableChanges테이블에 XML파일이 분해가 되어서 저장이 된다.