469,646 Members | 1,385 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,646 developers. It's quick & easy.

TIMESTAMP : created & modified fields ?

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
2 4306
"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
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.

Similar topics

reply views Thread by Bruno Batarelo | last post: by
7 posts views Thread by laurenq uantrell | last post: by
1 post views Thread by Roger Twomey | last post: by
5 posts views Thread by Prabu Subroto | last post: by
1 post views Thread by Joseph Geretz | last post: by
reply views Thread by presley2 | last post: by
7 posts views Thread by JJ | last post: by
1 post views Thread by mhearne808[insert-at-sign-here]gmail[insert-dot-he | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.