Activity Log Design

[former_SQLA_member1694868]

How would you design an activity log for a table MyTable in SQL Anywhere?

For simplicity assume that only one table's activity needs to be logged.

I'm thinking there would be Insert, Delete, and Update triggers on MyTable to insert into the ActivityLog table something like:

ActivityID integer default autoincrement,
UserID integer,
OnDate timestamp ,
Type char(1), // (insert, delete, update)
KeyID integer, // of affected row
FieldUpdated varchar(50), //(null for insert/delete)
FieldType // ?
OldValue  // ?
NewValue  // ?

Based on that structure (which may not be the best solution), Inserts and Deletes are fairly easy. However, for an Update the trigger would have to compare every field's new value against its old value to determine the inserts to make, and you would need to remember to adjust the trigger if the table structure changed.

Is there some system function or other way to specifically identify only the fields updated that caused the trigger to fire to avoid defining each column individually in a comparison?

Any thoughts, hints, or general directions are appreciated.

[Breck_Carter · Accepted solution]

See: Tip: Triggering an Audit Trai

Also: Revisited: Triggering an Audit Trail

Plus: ALTER TABLE and the Audit Trail

The code described has been in production in one application for several years, and the resulting data has been invaluable when tracking down the cause of various problems.