监控和记录 SQL SERVER 2005 中的 DDL 更改

• 下载 ddldatabasetriggerscript - 1.38 KB

引言

在执行任何规模较大的 IT 项目时，审计项目过程中发生的任何结构性变更非常重要。管理 5 到 50 名开发人员的团队的项目经理无法完成所有活动。另一个挑战是，如果团队规模小于 10 人，客户通常不同意聘请专职 DBA，认为没有必要执行重大活动。在这种情况下，开发人员将同时充当前端开发人员、中间件操作员和数据库操作员。

背景

SQL Server 2005 扩展了触发器功能，您通常将其与数据操作语言 DML（例如 insert、update 和 delete 命令）一起使用，以包含数据定义语言 (DDL) 命令，例如 CREATE Database、Drop Table、Alter Table。这项新技术有两种形式：

a. 服务器级别触发器（响应服务器上的更改）

b. 数据库级别触发器（特定于给定数据库的更改）

定义 DDL 触发器

触发 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 或 UPDATE STATISTICS 语句（DDL 触发器）

CREATE TRIGGER trigger_name 
ON { ALL SERVER | DATABASE } 
[ WITH  [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement  [ ; ] [ ...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

 ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

 ::=
    assembly_name.class_name.method_name

定义 DDL 触发器时，必须确定触发器的作用域。作用域决定了触发器是在数据库级别还是服务器级别执行。触发器作用域：在以下示例中，DDL 触发器安全将在数据库中发生 DROP TABLE 或 ALTER TABLE 事件时触发。

CREATE TRIGGER safety 
ON DATABASE 
FOR DROP_TABLE, ALTER_TABLE 
AS 
   PRINT 'You must disable Trigger "safety" to drop or alter tables!' 
   ROLLBACK
;

EVENTDATA() 函数返回 XML 数据，其中包含事件时间、系统进程 ID (SPID) 以及触发触发器的事件类型等信息。DDL 触发器使用 EVENTDATA() 函数来确定其如何响应。以下是 EVENTDATA() 函数返回的 XML 数据示例：

实现数据库作用域 DDL 触发器

实现数据库 ddl 触发器的目的是捕获更改。因此，第一个目标是创建一个表，该表可以保存有关数据库中发生更改的数据，并记录日期和时间。

表结构如下：

CREATE TABLE [dbo].[DatabaseLog](
    [DatabaseLogID] [int] IDENTITY(1,1) NOT NULL,
    [PostTime] [datetime] NOT NULL,
    [DatabaseUser] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Event] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Schema] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Object] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TSQL] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [XmlEvent] [xml] NOT NULL,
 CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED 
(
    [DatabaseLogID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

创建数据库级别触发器以将记录记录到上表中

    
CREATE TRIGGER [ddlDatabaseTriggerLog] 
ON DATABASE 
FOR DDL_DATABASE_LEVEL_EVENTS 
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @data XML;
    DECLARE @schema sysname;
    DECLARE @object sysname;
    DECLARE @eventType sysname;

    SET @data = EVENTDATA();
    SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
    SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
    SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname') 

    IF @object IS NOT NULL
        PRINT '  ' + @eventType + ' - ' + @schema + '.' + @object;
    ELSE
        PRINT '  ' + @eventType + ' - ' + @schema;

    IF @eventType IS NULL
        PRINT CONVERT(nvarchar(max), @data);

    INSERT [dbo].[DatabaseLog] 
        (
        [PostTime], 
        [DatabaseUser], 
        [Event], 
        [Schema], 
        [Object], 
        [TSQL], 
        [XmlEvent]
        ) 
    VALUES 
        (
        GETDATE(), 
        CONVERT(sysname, CURRENT_USER), 
        @eventType, 
        CONVERT(sysname, @schema), 
        CONVERT(sysname, @object), 
        @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'), 
        @data
        );
END;

GO

关注点

You will get a table with all changes recorded and that can be used as vital audit information at any level of project.

a. If you create manually using the sql management studio it will work
b. If you run the script for database table alter, drop or create it will work
c. If you change the constraint it would log 
d. if you break indexes or create them it will log