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

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