Who Made That Schema Change? A DDL Trigger SQLChallenge (28 minutes)

In this challenge, you get to take a crack at writing a DDL Trigger to track schema changes.

We’ll also cover some gotchas

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.

For this reason, you are challenged not only to create a DDL trigger, but also to test the trigger by also causing the trigger to fire when run in the context of a specific user. Don’t worry, there’s code for it all in the challenge.

Scripts

Grab the scripts at https://github.com/LitKnd/SQLWorkbooks/tree/main/ddl_trigger_challenge

Interested in using Event Notifications instead of DDL Triggers?

Totally cool, those are covered in this alternate SQLChallenge. Do both challenges to understand how the two approaches compare.

Lessons