How to Create a LastUpdated and GUID that Automatically Update

I was looking for a way to have all my tables have a unique GUID (Globally Unique ID) as well as a “Last Updated” column that would automatically update both on INSERT and on UPDATE.

Through a few hours of experimentation, I created the following:

 

 

I now add these functions to all my table templates.

The good stuff is the TRIGGER to keep my lastUpdated column updated, and the DEFAULT settings of the guid and lastUpdated columns in the CREATE statement. The DEFAULT keywords only help for INSERTs, which is why I had to also have the trigger.

The guid never changes once created on INSERT, but the guid column has to be there for INSERT and every subsequent UPDATE. Both are here in an easy to use example that you can run, and get:

Note the lastUpdated column value before & after the UPDATE. I hope this proof of concept helps you!

MS SQL 2008 R2

pat
:)

Leave a Reply

Your email address will not be published. Required fields are marked *