CODEWITHSUNDEEP
sql-servertriggersddl-trigger
Serguei
Serguei

Reputation: 2958

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

Upvotes: 4

Views: 936

Answers (1)

Yuck
Yuck

Reputation: 50845

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.

Upvotes: 2

Related Questions