Hi,

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