By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,686 Members | 2,708 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,686 IT Pros & Developers. It's quick & easy.

TIMESTAMP : created & modified fields ?

P: n/a
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)?

Jan 4 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"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.
Jan 4 '06 #2

P: n/a
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.
Jan 5 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.