Fire trigger on DISABLE TRIGGER

Question

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 ENABLE 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?

Answer 1

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.