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.

[thomas_duemesnil]

I thought it would be a good idea to use Trigger operation conditions.

if UPDATING( 'Column Name' ) then 
    ...
end if;
if UPDATING( <Variable> ) then 
    ...
end if;

But it is not possible to use a variable instead of a hard coded character string.

We use PowerDesigner to manage our DB Schema. We have enhanced it so that it will generate all needed triggers based on the data model.

[VolkerBarth]

Just a few thoughts:

I guess you will use AFTER ... FOR EACH ROW triggers to log the contents. For UPDATE, they have the advantage to be fired only when the columns's values are really changed, i.e. the following won't trigger the trigger:

UPDATE myTable set myCol = myCol;

(CAVEAT: Note that in a case-independent database, a change of col1 from 'a' to 'A' is considered a change (and the trigger is fired) even if both values are the same when compared.)

We do use a similar method to record a "history" of several tables but do only monitor a list of "relevant columns". For these, we use "AFTER UPDATE OF <column list="">" triggers to log if any of these columns has been modified. However, we do log the row contents of all columns before and after, so we do not log only the modified columns.

---

If you really want to log only modified columns, and want to have exactly one log entry for each modified column, a straightforward (but possibly non-performant) method would be to define an AFTER UPDATE OF <mycolumn> trigger for each of these columns. The "comparison logic" (i.e. whether the column has been changed) would then be coded in the trigger's definition itself and not in its body. These triggers will obviously be very easy to code.

The obvious drawback (besides a possible inefficiency) would be that you will need to add an AFTER UPDATE OF trigger for each new column that should be monitored. On the other hand, adding a new trigger might be easier/less error-prone than modifying one "big" trigger.

---

Another soulution would be to use only one AFTER UPDATE OF <list of all relevant columns> trigger and then to use one IF UPDATE(myColumn) test for each relevant column to distinguish between the several columns, something like

CREATE TRIGGER TRU_MyTable AFTER UPDATE OF column2, column3, ...
ON MyTable
REFERENCING OLD AS O
REFERENCING NEW AS N
FOR EACH ROW
BEGIN
   ...
   IF UPDATE(column2) THEN
      -- log column2 modification
   END IF;
   IF UPDATE(column3) THEN
      -- log column3 modification
   END IF;
   ...
END;

Note: I have not tested whether the test for UDATE(column) is only true when its value has been changed - or is true when it has been used in the UPDATE's SET clause.