I need to delete a disabled DML trigger on one of our tables.

Problem is the table is critical to production so I don't want to cause any delays.

Therefore my question. When you drop a trigger does it run an Alter on the table on the inside? Or otherwise affect the table?

I know that disabling a Trigger requires running an Alter. But I'm unsure what exactly DROP TRIGGER does step by step. And if that could lock the table and prevent any other queries from running.

Edit: I've looked at the official MS website and it says "The table and the data upon which it is based are not affected." But it leaves it a bit ambiguous as to whether the table is locked for the duration of the DROP. Also it states that ALTER permissions are required, which makes me concerned that ALTER is actually run, under the hood.

CodePudding user response：

If you test this you'll see that the DROP TRIGGER does require an exclusive lock on the table. It's a metadata-only operation so it shouldn't lock the table for long. But if the table is busy the DROP TRIGGER session's pending X or Sch-M lock will block other sessions new requests for locks on the table. If all the tasks the DROP TRIGGER session is waiting behind are short-running, then it will quickly get the locks it needs and complete its work.

However if the table is used by long-running queries then the blocking caused by the waiting DROP TRIGGER can be significant. So either wait for a maintenance window, or simulate waiting at low priority (which isn't available for DROP TRIGGER) by setting a lock_timeout and using a retry loop, something like:

set lock_timeout 500
while 1=1
begin
    begin try
       drop trigger SomeTrigger;
       break;
    end try
    begin catch
      if ERROR_NUMBER() <> 1222
        throw;
      RAISERROR ('retrying to drop trigger after lock timeout', 0, 1) WITH NOWAIT 
    end catch
end

The longer the lock_timeout the better the chances of success, but the more impact you might have on other sessions.