Re: entry log

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