Sql-server – lock escalation issue when using DDL trigger

ddl-triggerlock-escalationsql serverssmstrigger

I have a problem, I am trying to create a log table for my database so I can keep track of changes. I have created the following trigger that runs when a table is altered, created and dropped:

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

But for example I change a name of a column in a table the event data commandtext XML shows this

<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>

Instead of the full command. How do I stop this from locking and letting me see the full command?

Best Answer

Is is preferred that you post a full repro. I tried to repro this and below code returned "ALTER TABLE" and not the text you posted.

My guess is that you use some tool to modify the table and this tool starts by changing lock escalation before doing the real stuff. Just a guess, though, since we don't have a repro.

Anyhow, here's a repro using your trigger code and just doing a SELECT of the data you return from EVENTDATA.

And, as @Mo64 mentioned, you should pick up the correct element from EventData. See the second SELECT statement in the trigger code (the first is from your code):

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

Also, your trigger code is strange. You pick up the data from EVENTDATA several times, without picking up the value that you really want (the value that is to go into existing_table_name and new_table_name). Dig into XPath a bit more to get the values you really want. Below is a snippet from a DDL trigger that captures a couple of element values, perhaps that might get you started:

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)');
Related Question