From: Pooly (pooly7gmail.com)
Date: Tue Oct 04 2005 - 02:15:36 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

2005/10/3, s. keeling <keelingspots.ab.ca>:
> I'd like to add a bit of history data to a table (who changed a record
> last, and when it was last changed). Is this the way to do it?
>
> alter table MEMBERS
> add CHG_BY varchar(3)
>
> alter table MEMBERS
> alter CHG_BY set default "sbk"
>
> alter table MEMBERS
> add CHG_DATE date
>
> alter table MEMBERS
> alter CHG_DATE set default CURRENT_DATE
>
> Whoever next ends up with this can set CHG_BY's default to their
> initials and carry on from there.

It will be best to ensure the good value in your application, I'm not
sure everyone is willing to fill more fields in a query that track him
back.
You better go with an extra table to track all changes, and not only
the last one.
Something like members_changes (member_id, chg_by, chg_date)
btw, you could do :
alter table MEMBERS add CHG_BY varchar(3) not null default "sbk", add
CHG_DATE date not null default CURRENT_DATE on update current_date
(not tested)

--
Pooly
Webzine Rock : http://www.w-fenec.org/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]