4

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?

AdamL
  • 12,421
  • 5
  • 50
  • 74
Serguei
  • 2,910
  • 3
  • 24
  • 34

1 Answers1

2

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.

Yuck
  • 49,664
  • 13
  • 105
  • 135
  • Yeah that's an interesting idea. I guess the fact that you can't put a trigger on `{EN,DIS}ABLE TRIGGER` must be by design - otherwise you could get the SQL server into a pretty nasty state which you can't leave because triggers blow up everything you do including attempts to fix the triggers. – Serguei Sep 01 '11 at 18:25
  • There is a problem with this solution in 2008 http://connect.microsoft.com/SQLServer/feedback/details/509242/fire-a-ddl-trigger-when-the-new-syntax-disable-trigger-is-executed –  Jul 10 '12 at 16:34