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. Also I’m using ORIGINAL_LOGIN because it will return who made the change even if they are impersonating someone else.
For my test, I created a user that only has db_DDLADMIN on the database. That means it can make DDL changes but can’t insert, update, delete or even run a select against any table in the database. That’s why I grant INSERT to public for the logging table.
It’s a good way of knowing when unexpected changes happen, too.
Recently I assisted on a customer issue where customer wasn’t able to alter a memory optimized table with the following error
Msg 41317, Level 16, State 3, Procedure ddl, Line 4 [Batch Start Line 35]
A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.
If you access a memory optimized table, you can’t span database or access model or msdb. The alter statement doesn’t involve any other database.
It turns out there was a DDL trigger defined on the instance that wrote data to msdb. Click through for Jack’s repro script. I’d be able to use memory-optimized tables a lot more frequently (to the chagrin of company DBAs, perhaps) if they supported cross-database operations.
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.
SQL Server 2016 supports AFTER triggers! I could find no good example of how to do this with a project I’m working on, so I figured out how to make this work. The following before and after screenshots are from a SQL Server 2016 instance and the DVDStore3 package. I modified a trigger to work with In-Memory OLTP.
Click through to see the code.
DDL triggers and the events they handle are run within the same transaction, which can be rolled back. This is a powerful and convenient feature. It gives you the ability to programmatically “undo” undesirable events. Let’s look at a variation of the previous DROP_TABLE trigger we created. This script will create a temporary logging table, drop the previous trigger, and create it again. This time, the trigger will roll back the transaction, preventing the table from being dropped. Then it inserts a message to the log table.
There are a few gotchas here, to be sure, but DDL triggers are powerful tools.
This use of the UPDATE function for selective logging can be very useful when used on tables with columns such as: LastOrderDate, LastLoginDate, etc as these columns are often updated but those changes are probably not required to be logged.
One interesting point is that even if our trigger was configured to fire on DELETEs, the UPDATE function would not return true and therefore the change would not be logged. This makes sense as a DELETE affects all columns, so checking for a particular column is not required. If we wanted to log DELETEs to our ProductPriceLog table, we would use a trigger that fired on DELETEs.
But check the comments to make sure you know when UPDATE() fires—it’s not just when a particular column changes values.