Обсуждение: entry log

Hi,

When I started with MySQL I exploited their "bug" with timestamp
fields and always had a entered and updated field on my tables.

My question, is this interesting information enough to save on the
table itself? If so, I guess this could easily be solved with a
trigger, however, should one instead create a log table and log
changes, etc?

Hints and tips are appreciated :)

--
        regards,
        Robin

On Aug 19, 2007, at 14:04 , Robin Helgelin wrote:

> When I started with MySQL I exploited their "bug" with timestamp
> fields and always had a entered and updated field on my tables.
>
As I'm blissfully ignorant of MySQL's peculiarities, without a more
detailed explanation of what you're trying to do, I'm not sure if
this suggestion will help, but here I go anyway:

If you want created and updated timestamps, you can do something like
this:

CREATE TABLE foos
(
     foo text PRIMARY KEY
     , created_at TIMESTAMP WITH TIME ZONE NOT NULL
         DEFAULT CURRENT_TIMESTAMP
     , updated_at TIMESTAMP WITH TIME ZONE NOT NULL
         DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO foos (foo) VALUES ('a foo');
SELECT *
FROM foos;
   foo  |          created_at           |          updated_at
-------+-------------------------------+-------------------------------
a foo | 2007-08-19 15:18:27.271103-05 | 2007-08-19 15:18:27.271103-05
(1 row)

UPDATE foos
SET updated_at = DEFAULT
     , foo = 'foo b'
WHERE foo = 'a foo';
SELECT *
FROM foos;
   foo  |          created_at           |          updated_at
-------+-------------------------------+-------------------------------
foo b | 2007-08-19 15:18:27.271103-05 | 2007-08-19 15:18:35.575783-05
(1 row)

> My question, is this interesting information enough to save on the
> table itself? If so, I guess this could easily be solved with a
> trigger, however, should one instead create a log table and log
> changes, etc?

As you mention, you could use a trigger instead of explicitly setting
updated_at to DEFAULT, which might be more convenient because you
don't need remember to set the updated_at column explicitly on update.

Whether or not this information is *interesting* is really up to the
specifics of your application, rather than answerable in a general
sense.

Hope that helps.

Michael Glaesemann
grzm seespotcode net

On 8/19/07, Michael Glaesemann <grzm@seespotcode.net> wrote:
> As you mention, you could use a trigger instead of explicitly setting
> updated_at to DEFAULT, which might be more convenient because you
> don't need remember to set the updated_at column explicitly on update.
>
> Whether or not this information is *interesting* is really up to the
> specifics of your application, rather than answerable in a general
> sense.

I'm thinking it's probably going to make more sense to have a
logging/history table. What's the use of seeing when an entry was
updated when you don't know what was updated anyway :).

I guess that could be solved with triggers, each table have a trigger
that fires on update and runs a stored procedure.

--
        regards,
        Robin

Le dimanche 19 août 2007, Robin Helgelin a écrit :
> My question, is this interesting information enough to save on the
> table itself? If so, I guess this could easily be solved with a
> trigger, however, should one instead create a log table and log
> changes, etc?
>
> Hints and tips are appreciated :)

I've found this document a great read about version history management in
database:
  http://rueping.info/doc/Andreas Rüping -- 2D History.pdf

Regards,
--
dim