DDL Events

Dave Mason looks at DDL triggers:

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.

Related Posts

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

Triggers And Memory-Optimized Table Modifications

Jack Li troubleshoots a customer issue when trying to modify a memory-optimized table: 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 […]

Read More

Categories

July 2016
MTWTFSS
« Jun Aug »
 123
45678910
11121314151617
18192021222324
25262728293031