아직도 꿈만 꿔? :: DDL 트리거

2009/01/30 13:44

DDL 트리거

2009/01/30 13:44 in SQL Server

제가 알고 있는 바로는 두 가지 형태의 DDL 트리거가 있습니다.
하나는 데이터베이스 DDL 트리거이고 다른 하나는 서버 DDL 트리거입니다.

다음은 master 데이터베이스에 DDL 감사 로그 테이블을 생성하고 데이터베이스 DDL 이벤트 로그와 서버 로그인 DDL 이벤트 로그를 추적하는 DDL 트리거를 생성하는 스크립트입니다.

-- DDL 트리거
use master;
go
-- 감사테이블
create table dbo.audit_ddl_event
(
lsn int not null identity
, event_data xml not null
, constraint pk_audit_ddl_event primary key(lsn)
);
go

--------------------------------------------------------------------------------------
/* 서버DDL 트리거생성*/
use master;
go
create trigger trg_audit_ddl_login on all server
for ddl_login_events
as

declare @event_data as xml;
set @event_data = eventdata();

insert into master.dbo.audit_ddl_event(event_data)
values(@event_data)
go

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------
/* 모든데이터베이스에DDL 트리거생성스크립트만들기*/

use master;

declare @query nvarchar(max)
, @name nvarchar(128)

declare db_cur cursor
for select name from sys.databases where name <> 'tempdb'

open db_cur

fetch next from db_cur into @name

while @@fetch_status = 0
begin

set @query = N'
-- db범위ddl 트리거생성

print ''use ' + @name + ';''

use ' + @name + ';
go
begin try
drop trigger trg_audit_ddl_event on database
print ''trg_audit_ddl_event 트리거를삭제했습니다.''
end try
begin catch
print ''trg_audit_ddl_event 트리거가없어서삭제할수없습니다.''
end catch
go

create trigger trg_audit_ddl_event on database
for ddl_database_level_events
as

declare @event_data as xml;
set @event_data = eventdata();

insert into master.dbo.audit_ddl_event(event_data)
values(@event_data);
go
print ''trg_audit_ddl_event 트리거를만들었습니다.''
print ''''
go
'
print (@query);

fetch next from db_cur into @name
end

close db_cur
deallocate db_cur

go

--------------------------------------------------------------------------------------

-- ddl 이벤트발생
use test;
create table t1(id int)
drop table t1;
go

-- 데이터베이스레벨ddl 이벤트조회
select
cast(event_data.query('data(//DatabaseName)') as varchar(23)) database_name
, cast(event_data.query('data(//PostTime)') as varchar(23)) post_time
, cast(event_data.query('data(//EventType)') as sysname) event_type
, cast(event_data.query('data(//LoginName)') as sysname) login_name
, cast(event_data.query('data(//SchemaName)') as sysname) schema_name
, cast(event_data.query('data(//ObjectName)') as sysname) object_name
, cast(event_data.query('data(//TargetObjectName)') as sysname) target_object_name
, replace(cast(event_data.query('data(//TSQLCommand)') as nvarchar(max)),'','\n') as tsql_command
from master.dbo.audit_ddl_event;
go

create login jeidee2 with password = '1234';
alter login jeidee2 with password = 'xxx';
drop login jeidee2;
go

이올린에 북마크하기