Trying to add a line to a trigger that is attached to a table that is constantly in use
ALTER TRIGGER [dbo].[name_of_trigger] ON [dbo].[name_of_table] AFTER UPDATE
BEGIN
IF ORIGINAL_LOGIN() in ('username') RETURN
....somecode
END
Getting deadlocked and the only solution appears to be retrying every couple of seconds, which so far hasn't worked. Is there any other way to get my if condition in other than hitting F5 and hoping it works?
Thanks,
Craig
When you issue an ALTER TRIGGER statement, that process will attempt to acquire a SCH-M (schema modification) lock on the table object as well as the trigger object. This is an extremely low-concurrency lock and will cause the expected blocking that you're running into.
See this reference for a chart on lock compatibility. For referencing sake, here is the chart linked directly from that document:
As you can see, that SCH-M lock will conflict and cause a concurrency blocker.
Much like anything that can cause an interruption, or be blocked itself, I'll have to agree with the comment above (from David Crowell). You simply need to find a time when there is little to no activity on these objects.
SET DEADLOCK_PRIORITY