Fire trigger on DISABLE TRIGGER

There's a safety trigger that blocks all SQL DDL events (ALTER/DROP/CREATE etc.) on a production database instance.

For deployments you'd do DISABLE TRIGGER and then ENA开发者_如何学PythonBLE TRIGGER when done.

I'd like an Operator to be notified (EXEC sp_notify_operator ...) when the safety trigger is DISABLED/ENABLED. They don't appear to be DDL events and I can't add an UPDATE/DELETE trigger on sys.triggers either. Any ideas?

Since you're already "protected" so to speak from DDL statements being executed, you could add another database trigger looking for DDL events that calls a procedure to notify an operator. You might need another layer of management though - maybe something to queue the notifications - so that it doesn't become too spammy. I could envision changes being rolled out and receiving 100+ email notices...yuck.

CREATE TRIGGER DatabaseDDLNotices
ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
AS BEGIN
  -- place something into a queue to be batched later
END;

In my opinion this also has the nice side effect of keeping notification logic and DDL prevention logic separated.