updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT

How can we help you today?

Start a new topic

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Trevor Daniel

started a topic almost 8 years ago

I want to monitor a table to see if any of the records have been updated.

Exactly like this question on StackOverflow:

http://stackoverflow.com/questions/307438/how-can-i-tell-when-a-mysql-table-was-last-updated

(The answer with 20 upvotes)

So, I need a column called "updated_at" which has a default value similar to a value that would be created by the sql:

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

I am guessing it's something to do with the "Default Value" of a column?

Can anyone explain how I can add this to an existing table please?

Thanks

Trev

2 Comments

Trevor Daniel

said almost 8 years ago

Works perfectly!! many thanks!

Rob Parker

said almost 8 years ago

I'm not finding a way to do it with SQL commands in VistaDB, but via Data Builder or the API from .NET there is a property (DefaultValueUseInUpdate) which you can set on a column definition to also "Use Default Value In Update (Not SQL Standard)". In Data Builder right click on the Columns node under the table you want and select Add New Column, or right-click on the table node itself and Alter Table and then switch over to the Column Details tab to get to the same form without automatically creating a new place-holder column.

You'll want to use a type such as DateTime or DateTime2, and use GetDate() as the value for the default. I think that will then do what you are describing (it worked in a quick test).