A Fine Slice Of SQL Server
David Klee shows that you can put an AFTER trigger on a memory-optimized table in SQL Server 2016:
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.
Comments closedDave 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.
Comments closedThis 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.
Comments closed