MSSQL数据库触发器，记录数据库相关变动

--第一步：建库建表

if  exists(select 1 from master.dbo.sysdatabases where name='AuditDB')

drop database AuditDB

go



create database AuditDB

go

use AuditDB

go

if object_id('DDLEvents','u')is not null

drop table DDLEvents

go

create table DDLEvents(

    EventDate datetime default (getdate()) NOT null,      --事件时间

    EventType nvarchar(64) null,                          --事件类型

    EventDDL nvarchar(max) null,                          --事件内容

    EventXML xml null,                                    --事件xml

    databaseName nvarchar(255) null,                      --数据库名称

    SchemaName nvarchar(255) null,                        --架构名

    ObjectName nvarchar(255) null,                        --用户

    HostName varchar(64) null,                            --计算机名称

    IPAddress varchar(32) null,                           --IP地址

    ProgramName nvarchar(255) null,                       --SQLServer版本

    LoginName nvarchar(255) null                          --登录名

)

go

--第二步：在需要监控的库上执行这个脚本，对DDL操作会记录在第一步中的库中

if  exists(select * from sys.triggers where parent_class_desc= 'database' AND name = N'DDLtriggertTrace')

disable trigger DDLtriggertTrace on database

if  exists(select * from sys.triggers where parent_class_desc= 'database' AND name = N'DDLtriggertTrace')

drop trigger DDLtriggertTrace on database

go

create trigger DDLtriggertTrace on database

    --捕获存储过程、视图、表的创建、修改、删除动作

for create_procedure, alter_procedure, drop_procedure, create_view,

        alter_view, drop_view, create_table, alter_table, drop_table

as

    begin

        set nocount on ;

        declare @EventData xml = eventdata() ;--返回有关服务器或数据库事件的信息，以xml格式保存。

        declare @ip varchar(32) =( select  client_net_address

                                    from    sys.dm_exec_connections

                                    where   session_id = @@SPID

                                  ) ;



        insert  AuditDB.dbo.DDLEvents

                ( EventType ,

                  EventDDL,

                  EventXML,

                  databaseName,

                  SchemaName,

                  ObjectName,

                  HostName,

                  IPAddress,

                  ProgramName,

                  LoginName

                )

                select  @EventData.value('(/EVENT_INSTANCE/EventType)[1]','NVARCHAR(100)') ,

                        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]','NVARCHAR(MAX)') ,

                        @EventData ,

                        DB_NAME() ,

                        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]','NVARCHAR(255)') ,

                        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]','NVARCHAR(255)') ,

                        HOST_NAME() ,

                        @ip ,

                        PROGRAM_NAME() ,

                        SuseR_SNAME() ;  

    end

go