触发以捕获服务器中的架构更改 [英] Trigger to capture schema changes in the Server

问题描述

是否可以实现类似以下触发器的内容

Is it possible to implement something like the following trigger

CREATE TRIGGER [tr_AU_ddl_All_Server] ON DATABASE
    WITH EXECUTE AS self
    FOR DDL_DATABASE_LEVEL_EVENTS
AS
    DECLARE
        @data XML
      , @rc INT
    SET @data = EVENTDATA()
    EXEC @rc = __AU.dbo.AU_DDLLog @data
GO

在整个服务器上。我的想法是捕获服务器中所有数据库中的所有架构更改。

BUT on the whole server. My idea is to capture all schema changes across all the databases in the server.

就即时消息而言，这在SQL Server 2005中是不可能的，但是我想知道是否有人有这样的工作。我想避免在每个数据库中都必须实现触发器。

As far as im concerned this is not possible in SQL Server 2005, but I'd like to know if anyone got something like this to work. I'd like to avoid having to implement a trigger in every single database.

推荐答案

是的，SQL Server 2005引入了 DDL触发器-在 SQL团队。

Yes, SQL Server 2005 introducted the "DDL Triggers" - read an excellent article on it here at SQL Team.

本文很好地说明了它们是DDL触发器的两个作用域-服务器范围或数据库范围。整个数据库范围的数据库都无法应用于整个服务器-您必须在每个数据库中进行设置。

This article shows nicely that they are two scopes for DDL triggers - server-wide, or database-wide. Those that are database-wide cannot be applied to the whole server - you'd have to set them up in each database.

Marc

这篇关于触发以捕获服务器中的架构更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！