We have used the method you outlined for some time. We also use another
approach which is to use a trigger to record changes on a field by field
basis This is easier to read and works better when the changes are
typically on a small number of fields each time. The log table looks like
this

Rec User Timestamp field_name old_value new_value

The values are always cast to varchars

Regards
Paul

>
> I have to implement a "record history" feature, ie when a
> record is updated (or deleted) changes should be recorded so
> that administrator can later see who did what and when. I
> come up with this: for each table which needs history create
> a log table with exactly the same structure and then use an
> trigger to log changes, ie
>
> CREATE TABLE TAB_X (
> UID INTEGER PRIMARY KEY,
> ...
> );
>
> CREATE TABLE TAB_Log_X (
> UID INTEGER,
> ...
> );
>
> CREATE TRIGGER X_Log FOR TAB_X
> ACTIVE BEFORE INSERT OR UPDATE OR DELETE POSITION 32767 AS BEGIN
> IF(NOT INSERTING)THEN BEGIN
> INSERT INTO TAB_Log_X SELECT * FROM TAB_X WHERE(UID = OLD.UID);
> END
> /* also insert record into special log table with
> CURRENT_USER, CURRENT_TIMESTAMP, operation (UPD/INS/DEL) etc
> information */ END;
>
> Now one thing I'm wondering about is is it better to use
> BEFORE or AFTER trigger. In the BEFORE trigger I can use
> "INSERT ... SELECT * FROM" (easy!) while in the AFTER trigger
> I must list every single field in INSERT statement and use
> OLD.field variables. The first way also ensures that when
> altering the stucture of source table the prg won't work
> until I have fixed destination table as well. But perhaps
> there is some good reason why AFTER trigger should be used?
>
> Or can somebody suggest an better way to keep "record history"?
>
>
> TIA
> ain