TIMESTAMP : created & modified fields ?

[Reddog]

Hi all,

I have a number of tables that I want 'timestamped' with both a
modified and created 'timestamp' field.

Ideally both fields should 'work if ignored' in insert and updates,
thus the following is fine for a modified timestamp field tsm:

tsm timestamp default current_timestamp on upate current_timestamp

But is there a way to create a field that in effect is 'write once'
and contains the creation timestamp ? In a sense I want some thing
like (psudo sql create line):

tsc timestamp default current_timestamp 'on_update_do_nothing'

I think a second timestamp with 'default current_time' would help but
mysql does not allow this - (I have no idea why !?)

There are quite a number of tables and I want to avoid 'table
specific' sql code like the following (psudo sql):

insert record ...
get last update auto increment key
select modifed timestamp value from key record
update key record create timestamp field with timestamp

Any ideas (for a 'write once' creation timestamp)?

[Bill Karwin]

"Reddog" <_n***************@blueyonder.co.uk> wrote in message
news:i1********************************@4ax.com...

I have a number of tables that I want 'timestamped' with both a
modified and created 'timestamp' field.

Any ideas (for a 'write once' creation timestamp)?

One method to do this is with triggers (requires MySQL 5.0). Define a
trigger on INSERT that forces the creation timestamp field to be the current
time, overriding any other value that the client may have attempted to
insert.

CREATE TRIGGER timestamp_ins_check BEFORE INSERT ON myTable
FOR EACH ROW
BEGIN
SET NEW.created = CURRENT_TIMESTAMP();
SET NEW.updated = CURRENT_TIMESTAMP();
END;

Also define a trigger on UPDATE that forces the creation timestamp field to
be unaltered from its pre-update value.

CREATE TRIGGER timestamp_upd_check BEFORE UPDATE ON myTable
FOR EACH ROW
BEGIN
SET NEW.created = OLD.created;
SET NEW.updated = CURRENT_TIMESTAMP();
END;

If you don't use MySQL 5.0, you may have to resort to application-level code
to enforce your rules.

Regards,
Bill K.

[Reddog]

On Wed, 4 Jan 2006 11:40:52 -0800, "Bill Karwin" <bi**@karwin.com>
wrote:

One method to do this is with triggers (requires MySQL 5.0). Define a
trigger on INSERT that forces the creation timestamp field to be the current
time, overriding any other value that the client may have attempted to
insert.
.... .....>If you don't use MySQL 5.0, you may have to resort to application-level code
to enforce your rules.

Regards,
Bill K.

Thanks for the info Bill,
I using MySql 4.1.7 NT so I have do not triggers yet.
Reddog.