Further, your reporting will skew the data -- let's say once a day you
want to know the last time that row was read. Well, the first day,
you'll get accurate numbers. The second day, though, you'll end up
seeing that each row was read at latest the day before, because you
read it searching for the date.
That's an UPDATE you really want to build into your APPLICATION layer.
It's a BAD idea to store business logic/rules in the data layer.
On 3/13/06, SGreen@stripped <SGreen@stripped> wrote:
> "fbsd_user" <fbsd_user@stripped> wrote on 03/13/2006 01:10:17 PM:
> > In my mysql 4.4 table definition the default attributes are (ON
> > UPDATE CURRENT_TIMESTAMP). Reading the manual my understanding is
> > this is saying that the auto timestamp update feature is active. The
> > manual does not say what the trigger is to make the timestamp in the
> > row to be bumped to the current timestamp. I have noticed that the
> > timestamp field is only bumped when I update a field in the row. I
> > would like it to be auto bumped every time the row is selected/read.
> > Is there a way to do this without creating a timestamp from date in
> > my php code and then updating the row instead of reading the row?
> > The final goal is to auto bump the timestamp every time the user
> > logs on.
> It sounds like there is a logon process that the user must perform. What's
> the issue you have about updating your timestamp from within that process?
> That's where it sounds most logical to me to add the code to bump your
> timestamp value.
> From a design point of view, it would seriously slow down the entire
> server if it had to check for something to do on EVERY read from ANY
> table. In order to do what you want the database to do, that facility
> would need to exist so that the engine could bump the timestamp
> automatically. It is generally much better to NOT write SELECT statements
> to a log or to do anything else that would slow them down. Checking every
> row that ever got selected to see if any column in it is an
> auto-update-on-select-timestamp column would do that in a major way. It
> also breaks all kinds of SQL rules to make a SELECT clause into something
> that modifies data. That would lead to all sorts of data chaos and I want
> no part of it.
> I am sorry, but if you want the timestamp changed you are going to have to
> initiate that change by an UPDATE statement.
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine