使用 DDL 觸發器時的鎖升級問題

我有一個問題，我正在嘗試為我的數據庫創建一個日誌表，以便跟踪更改。我創建了以下觸發器，該觸發器在更改、創建和刪除表時執行：

CREATE TRIGGER TableTrigger
ON DATABASE
FOR
   CREATE_TABLE,
   ALTER_TABLE,
   DROP_TABLE
AS
BEGIN
   SET NOCOUNT ON;
   INSERT INTO TableLog (
       EventDate,
       EventType,
       Existing_Table_Name,
       New_Table_Name,
       Changed_By
   )
   VALUES (
       GETDATE(),
       EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
       EVENTDATA(),
       EVENTDATA(),
       USER
   );
END;
GO

但是例如，我更改了表中列的名稱，事件數據命令文本 XML 顯示了這個

<TSQLCommand>
     <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
     <CommandText>ALTER TABLE dbo.Languages SET (LOCK_ESCALATION = TABLE)</CommandText>
</TSQLCommand>

而不是完整的命令。如何阻止它鎖定並讓我看到完整的命令？

最好是您發布完整的複製品。我試圖重現這個和下面的程式碼返回“ALTER TABLE”而不是你發布的文本。

我的猜測是您使用了一些工具來修改表，並且該工具首先在執行實際操作之前更改鎖升級。不過，這只是一個猜測，因為我們沒有復製品。

無論如何，這是一個使用您的觸發程式碼的重現，並且只是對您從 EVENTDATA 返回的數據進行選擇。

而且，正如@Mo64 所提到的，您應該從 EventData 中選擇正確的元素。請參閱觸發器程式碼中的第二個 SELECT 語句（第一個來自您的程式碼）：

CREATE TRIGGER TableTrigger
ON DATABASE
FOR
   CREATE_TABLE,
   ALTER_TABLE,
   DROP_TABLE
AS
BEGIN
SET NOCOUNT ON;

--Not the correct element:
SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)')

--This is the one you want:
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')
END;
GO

CREATE TABLE t(c1 int)
GO

ALTER TABLE t ALTER COLUMN c1 tinyint

另外，您的觸發程式碼很奇怪。您多次從 EVENTDATA 中獲取數據，但沒有獲取您真正想要的值（要進入 existing_table_name 和 new_table_name 的值）。深入研究 XPath 以獲得您真正想要的值。下面是一個 DDL 觸發器的片段，它擷取了幾個元素值，也許可以幫助您入門：

DECLARE @ev xml
SET @ev = EVENTDATA()
SELECT @ev
DECLARE @PostTime datetime2 = @ev.value('(/EVENT_INSTANCE/PostTime)[1]','datetime2');
DECLARE @LoginName sysname = @ev.value('(/EVENT_INSTANCE/LoginName)[1]','sysname');
DECLARE @TSQLCommand nvarchar(max) = @ev.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)');

不是 DDL 觸發器專家，但我認為您選擇了錯誤的事件數據。例如這裡是來自線上書籍的範例觸發器 DDL 觸發器 BoL

您似乎正在擷取程式碼中的事件類型

CREATE TRIGGER ColumnChanges

ON DATABASE

FOR ALTER_TABLE

AS

– 檢測列是否被創建/更改/刪除。

SELECT EVENTDATA().value(’(/EVENT_INSTANCE/TSQLCommand/CommandText) 1 ‘, ’nvarchar(max)’)

RAISERROR (‘無法在此數據庫中修改表架構。’, 16, 1);

回滾；

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