Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

share|improve this question
2  
Schedule a maintenance window. –  David Crowell Mar 20 at 14:46
    
SET DEADLOCK_PRIORITY –  Jon Seigel Mar 20 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 at 6:40

1 Answer 1

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
1  
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 at 16:12

Your Answer

 
discard

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.