4

Its possible gets a created and updated time of one row directly with sql/mysql? I know a method to got a table last update but i was wondering if there will be a quick natively way to do this with sql.

I made a way to do this creating automatically 2 fields in table(created_date and updated_date) but I decided to ask you first because there is probably a better way to do this.

I hear suggestions! Thanks for help.

Stefan Luv
  • 1,179
  • 3
  • 12
  • 28
  • As far a I can tell there's no built-in way to get this information. You have to add your own column(s) to do it. – aroth Sep 10 '12 at 01:09
  • http://stackoverflow.com/questions/307438/how-can-i-tell-when-a-mysql-table-was-last-updated?rq=1 – Jocelyn Sep 10 '12 at 01:15
  • @Jocelyn your link explains how u got a last update for a table, not for row in table – Stefan Luv Sep 10 '12 at 01:57

1 Answers1

1

I don't know of any way to do this without having two extra columns in the table - but what I do to update them is to have a database trigger do it rather than the application logic.

There are some benefits, but also some drawbacks to using a trigger approach.

Firstly, the benefits:

  • It is much easier to add it this way as an afterthought to an app. Very little changes to the appliation/site code. The trigger takes care of it all. (assuming you insert new rows by specifying field names)
  • The triggers will take care of any other ways to change the data - someone changing a row from a console would still cause the trigger to fire as would another application using the same database.
  • Allows the possibility (though I don't do it) of having a pure row inserted/updated table.

But some downsides too:

  • Unless you know that triggers are doing these updates, it can often be overlooked and the triggers become forgotten when migrating to another server etc
Fluffeh
  • 33,228
  • 16
  • 67
  • 80
  • im creating a ORM based on DataMapper, and im trying to add a option to recovers a last field edit time and update time, only if a user needs it to something (for example, gets a user register time)I was doubtful whether there was any direct method for SQL to retrieve this information .... if not exists, then no matter, I will continue using a real fields to store update/create time – Stefan Luv Sep 10 '12 at 01:56
  • @StefanLuv Yeah, it more more along the lines of having a single trigger on a table being easier to use than possibly having to modify many different sections of code to update the same table - and modifying all the queries/actions. – Fluffeh Sep 10 '12 at 02:49
  • well thats is all, the better option its a triggers, up to you and thanks for participate! – Stefan Luv Sep 10 '12 at 03:13