lock escalation issue when using DDL trigger
1
vote
2
answers
109
views
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)', '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
ALTER TABLE dbo.Languages SET (LOCK_ESCALATION = TABLE)
Instead of the full command. How do I stop this from locking and letting me see the full command?
Asked by Oliver Smith
(11 rep)
Dec 3, 2020, 12:58 PM
Last activity: Dec 3, 2020, 04:41 PM
Last activity: Dec 3, 2020, 04:41 PM