Sample Header Ad - 728x90

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