If you can consider buying a tool, IBLogManager.

I'm using it for years and it's very easy to setup and use, and becomes
almost transparent.

On 11/29/06, ainpoissee <ainpoissee@...> wrote:
>
> 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
>
>
>


[Non-text portions of this message have been removed]