SQL Server DDL触发器应用实例

-- 环境SQL Server 2008 R2

use Auditdb
go

-- 创建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

-- 创建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