Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

   IF ORIGINAL_LOGIN() in ('username')   RETURN



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?



share|improve this question
Schedule a maintenance window. – David Crowell Mar 20 '14 at 14:46
SET DEADLOCK_PRIORITY – Jon Seigel Mar 20 '14 at 17:05
Hello @JonSeigel, thank you for the hint, but basically even with the deaklock priority set higher for my session, I was still unable to get to a point where I had access to the table. – Craig Efrein Mar 21 '14 at 6:40
up vote 4 down vote accepted

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:

enter image description here

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.

share|improve this answer
I wish a maintenance window were possible, but lets just assume that someone did something in production that needs to be rolled back. Guess they will have to wait and deal with the consequences. – Craig Efrein Mar 20 '14 at 16:12

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.