复制代码
1-- 环境SQL Server 2008 R2
复制代码
1
2
3
4use Auditdb go
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26-- 创建DDL历史记录表 if object_id('dbo.DDLHistory','U') is not null drop table DDLHistory go create table DDLHistory ( LogDate datetime not null, DatabaseName varchar(128), ObjectType varchar(128), ObjectName varchar(128), EventType varchar(128), HostName varchar(128), ProgramName varchar(500), LoginName varchar(128), OriginalLoginName varchar(128), NtDomain varchar(128), NtUserName varchar(128), EventInfo xml constraint PK_DDLHistory primary key(LogDate) ) go use master go
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61-- 创建DDL触发器 create trigger [Tr_AuditDDL] on all server with execute as 'sa' for create_table, alter_table, drop_table, create_procedure, alter_procedure, drop_procedure, create_function, alter_function, drop_function as begin set nocount on declare @x xml, @tName varchar(128), @eType VARCHAR(128), @idoc int begin try select @x = eventdata() exec sys.sp_xml_preparedocument @idoc output, @x select @tName = ObjectName, @eType = EventType from openxml(@idoc,'//',0) with(ObjectName varchar(128) 'ObjectName', EventType varchar(128) 'EventType') where ObjectName is not null exec sys.sp_xml_removedocument @idoc insert into Auditdb.dbo.DDLHistory ( LogDate, DatabaseName, ObjectType, ObjectName, EventType, HostName, ProgramName, LoginName, OriginalLoginName, NtDomain, NtUserName, EventInfo ) select getdate(), @x.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(128)'), @x.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(128)'), @tName, @eType, [host_name], [program_name], login_name, original_login_name, nt_domain, nt_user_name, @x from sys.dm_exec_sessions where session_id = @@SPID end try begin catch print error_number() print error_message() end catch end go -- 启用DDL触发器 enable trigger [Tr_AuditDDL] on all server
最后
以上就是刻苦石头最近收集整理的关于SQL Server DDL触发器应用实例的全部内容,更多相关SQL内容请搜索靠谱客的其他文章。
本图文内容来源于网友提供,作为学习参考使用,或来自网络收集整理,版权属于原作者所有。
发表评论 取消回复