是否可以執行 ALL SERVER DML Trigger？

當客戶從另一家公司切換到我們時，我的部門致力於轉換客戶的數據庫。我們有一個 RDP 會話，他們可以連接並查看我們的軟體及其數據。一旦他們批准轉換，我們就會從我們的伺服器複製數據並在現場為他們進行設置。

AFTER在轉換批准階段，如果我們可以在連接到伺服器的所有數據庫上執行一些和触發器，這將使我們的事情變得簡單得多INSTEAD OF（我們的部門與開發軟體的部門不同，他們不會製作版本對於為我們內置的東西的測試站點）。

我看到兩個選項：

1. 創建一個 DML 觸發器，該觸發器在連接到伺服器的所有數據庫上執行操作，但是當數據庫移動到實時伺服器時，該操作不再發生，因為它是在伺服器而不是數據庫上執行的。
2. 有一個 DML 觸發器，如果它檢測到它不再連接到測試伺服器並且處於實時環境中，它會自行刪除。

從我閱讀的內容來看，我認為第一個選項是不可能的，但我想檢查一下。我想我足夠了解第二個選項，但是任何如何實現它的例子都會非常有幫助。

您將無法創建伺服器範圍的觸發器，因為它必須存在於發生 DML 更改的同一數據庫中。

擁有自刪除程式碼聽起來也有點問題。

我認為您將必須制定一個流程，在附加新數據庫時創建觸發器，並在完成它們時刪除它們。這很可能是一個手動過程。

在以下範例中，使用 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 擔任產品支持工程師

引用自：https://dba.stackexchange.com/questions/17357