TempDB DDL Triggers

Erik Darling wants to create a DDL trigger in tempdb which survives after restart:

One workaround is to create it in model. Then it will be in tempdb on restart, but it will also be in all the databases you create.

That may or may not have the intended effect! If you don’t want people doing a certain thing in just tempdb, you have to add an additional check using the event data.

Click through for more details, including a couple of sample scripts.

Related Posts

Trigger Nuance

Denis Gobo offers up some good advice on triggers: Most common mistake people make when first starting writing triggers is that they write it in such a way that it will only work if you insert/update/delete one row at a time. A trigger fires per batch not per row, you have to take this into […]

Read More

Tracking DDL Events

Kenneth Fisher has a simple database trigger to track certain data definition language events: A couple of notes before testing the code. The event groups I’m using will pull CREATE, ALTER and DELETE events for those objects. For a more complete list of events (you might want to add service broker events for example) go here. […]

Read More


January 2017
« Dec Feb »