主页 / dba / 问题 / 17357
Accepted
Scott Chamberlain
Asked: 2012-05-03 07:00:47 +0800 CST

是否可以执行 ALL SERVER DML Trigger？

  • 772

当客户从另一家公司切换到我们时，我的部门致力于转换客户的数据库。我们有一个 RDP 会话，他们可以连接并查看我们的软件及其数据。一旦他们批准转换，我们就会从我们的服务器复制数据并在现场为他们进行设置。

AFTER在转换批准阶段，如果我们可以在连接到服务器的所有数据库上运行一些和触发器，这将使我们的事情变得简单得多INSTEAD OF（我们的部门与开发软件的部门不同，他们不会制作版本对于为我们内置的东西的测试站点）。

我看到两个选项：

  1. 创建一个 DML 触发器，该触发器在连接到服务器的所有数据库上执行操作，但是当数据库移动到实时服务器时，该操作不再发生，因为它是在服务器而不是数据库上运行的。
  2. 有一个 DML 触发器，如果​​它检测到它不再连接到测试服务器并且处于实时环境中，它会自行删除。

从我阅读的内容来看，我认为第一个选项是不可能的，但我想检查一下。我想我足够了解第二个选项，但是任何如何实现它的例子都会非常有帮助。

sql-server sql-server-2005

2 个回答

  1. Best Answer

    您将无法创建服务器范围的触发器，因为它必须存在于发生 DML 更改的同一数据库中。

    拥有自删除代码听起来也有点问题。

    我认为您将必须制定一个流程，在附加新数据库时创建触发器，并在完成它们时删除它们。这很可能是一个手动过程。

    • 2

  2. 在以下示例中，使用 SQL Server 的 EVENTDATA() 函数捕获有关触发触发器的事件的信息。SQL 脚本创建 DDL 触发器，该触发器在数据库级别捕获 CREATE、ALTER 和 DROP 事件（尽管可以在服务器级别创建触发器以捕获服务器上所有数据库的事件；应该使用 ON ALL SERVER 选项，而不是在数据库上）：

    CREATE TRIGGER Audit_DDL
ON DATABASE
    FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
     DECLARE
        @event xml;
     SET
     @event = EVENTDATA();
     INSERT INTO Audit_DDL_Events
     VALUES
     (
     REPLACE(CONVERT(varchar(50),
     @event.query('data(/EVENT_INSTANCE/PostTime)')), 'T', ' ')
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/LoginName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/UserName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/DatabaseName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/SchemaName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/ObjectName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/ObjectType)'))
     ,
     CONVERT(varchar(max),
     @event.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
     );

    还必须为来自 EVENTDATA XML 的审计数据创建一个适当的存储表：

    CREATE TABLE Audit_DDL_Events
(
             DDL_Event_Time            datetime
             ,
             DDL_Login_Name            varchar(150)
             ,
             DDL_User_Name             varchar(150)
             ,
             DDL_Database_Name         varchar(150)
             ,
             DDL_Schema_Name           varchar(150)
             ,
             DDL_Object_Name           varchar(150)
             ,
             DDL_Object_Type           varchar(150)
             ,
             DDL_Command              varchar(max)
);

    现在，我们已经为跟踪 DDL 更改做好了准备，创建、修改或删除通过触发器捕获的表时的信息如下所示：

    在此处输入图像描述

    尽管我们已经证明这种方法可以产生可行的审计结果，但使用触发器跟踪 SQL Server 中的 DDL 更改有几个缺点。首先，具有足够权限的用户可以轻松禁用触发器。另一种避免通过触发器跟踪 SQL Server 中的 DDL 更改的方法是执行架构更改，然后从审核存储库中删除捕获的信息。在我们的例子中，只需从 Audit_DDL_Events 表中删除适当的条目即可。此外，如果不存在可以恶意执行 DDL 更改的特权用户，则此方法无法提供有关在设置之前发生的更改的信息

    您可以在跟踪 SQL Server 中的 DDL 更改 – 'Trouble with Triggers'在线文章中阅读有关该主题的更多信息

    免责声明：我在 ApexSQL 担任产品支持工程师

    • 1

相关问题