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

最新推荐文章于 2025-08-13 17:52:54 发布

原创最新推荐文章于 2025-08-13 17:52:54 发布 · 486 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#table #database #null #object #测试 #c

t_sql 程序设计_ddltrigger 专栏收录该内容

0 篇文章

订阅专栏

本文介绍了一个针对特定表xuwangjin的DDL触发器实现案例，该触发器仅记录创建和删除表的操作，并详细展示了触发器的SQL代码及测试步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

关于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
)