Who Made That Schema Change? An Event Notification SQLChallenge (27 minutes)

In this challenge, you get to take a crack at creating an Event Notification to track schema changes.

Event Notifications are loosely coupled with the calling code

We covered DDL Triggers in a past SQL Challenge. DDL Triggers are tightly coupled with the commands that call them: like other triggers, the code in the trigger becomes part of the transaction of the code that causes the trigger to fire.

Event Notifications are different: even if the event notification fails, the command that causes it to fire may go on none-the-wiser. That’s often desirable (but not always).

But there’s a reason that not everyone uses Event Notifications…

That’s because Event Notifications are built on top of the Service Broker technology. Service Broker never really caught on for use by developers and DBAs. You don’t have to take my word for this: read this post by Klaus, who wrote a post on Service Broker. If your implementation of Event Notifications hits an edge case, troubleshooting is pretty darn tricky.

Even so, I love having Event Notifications in my toolkit

For both DDL Triggers and Event Notifications, production environments are not the only use case– far from it! It can be useful to log schema changes and timings in development environments, particularly when troubleshooting code that may not have the best logging built into it already. And who knows? Maybe use in those non-production environments will make you confident enough with Event Notifications that you’re also ready to support them in production.

Course scripts

Grab the course scripts here: https://github.com/LitKnd/SQLWorkbooks/tree/main/event_notifications_challenge

Lessons