关于ddl_datebase 触发器 监控某张表的ddl操作

关于ddl_datebase  触发器
-- 记录xuwangjin表的 ddl操作
只针对于 create table 和 drop table 因为object本身为table -- 2009/01/04 am 0:50徐王锦--
-- 转贴请注明--
create trigger ddl_tr_xuwangjin on database
for ddl_database_level_events
as
declare @eventdata xml
select @eventdata = eventdata()
if @eventdata .value( ' (EVENT_INSTANCE/ObjectName)[1] ' , ' sysname ' ) = N ' xuwangjin '
    and @eventdata .value( ' (EVENT_INSTANCE/ObjectType)[1] ' , ' sysname ' ) = N ' TABLE '
begin
select
EventType = t.c.value( ' (EventType)[1] ' , ' sysname ' ),
PostTime = t.c.value( ' (PostTime)[1] ' , ' datetime ' ),
SPID = t.c.value( ' (SPID)[1] ' , ' int ' ),
ServerName = t.c.value( ' (ServerName)[1] ' , ' sysname ' ),
LoginName = t.c.value( ' (LoginName)[1] ' , ' sysname ' ),
TSQLCommand = t.c.value( ' (TSQLCommand/CommandText)[1] ' , ' varchar(1000) ' ),
@eventdata .value( ' (EVENT_INSTANCE/TSQLCommand/SetOptions/@ANSI_NULLS)[1] ' , ' sysname ' ) as ANSI_NULLS,
@eventdata .value( ' (EVENT_INSTANCE/TSQLCommand/SetOptions/@ANSI_NULL_DEFAULT)[1] ' , ' sysname ' ) as ANSI_NULL_DEFAULT,
@eventdata .value( ' (EVENT_INSTANCE/TSQLCommand/SetOptions/@AANSI_PADDING)[1] ' , ' sysname ' ) as ANSI_PADDING,
@eventdata .value( ' (EVENT_INSTANCE/TSQLCommand/SetOptions/@QUOTED_IDENTIFIER)[1] ' , ' sysname ' ) as QUOTED_IDENTIFIER,
@eventdata .value( ' (EVENT_INSTANCE/TSQLCommand/SetOptions/@ENCRYPTED)[1] ' , ' sysname ' ) as ENCRYPTED
from @eventdata .nodes( ' EVENT_INSTANCE ' ) as t(c)
end
else
return
go

-- 测试
-- 1
create table xuwangjin
(id smallint
)


-- 2
drop table xuwangjin