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.

[VolkerBarth]

FWIW: A completely different approach would be to use the auditing facility, which uses the transaction log to log relevant events. However, this does much more than you are trying to monitor, and you have to use DBTRAN to "read" the events, which may not appropriate for your usage.

[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.

[former_SQLA_member1694868 · Accepted solution]

I should have searched the blog first.

In the original example where all logged events are in one table the two questions "Show everything that happened today" and "Show me everything X user did today" are simple queries of the one log table.

With your example, I'm curious if you would use multiple unions or query each table independently to get those answers?

Also for an update, which creates two rows in the log table, do you then compare each column in the query/view to determine which field was updated?

[Breck_Carter · Accepted solution]

In the original example, each base table has its own log table... sorry for the confusion... so the "show me everything X user did today" is not such a simple query.

In practice using these tables has been very much an adhoc, manual process... just a bunch of selects, some where clauses and a lot of manual inspection. That's because they've been used only during forensic studies, each of which is involves a completely different situation.

If these tables were used for regular queries, then yes, some unions and views might be developed.

[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.

[former_SQLA_member1694868]

Excellent. So it looks like you keep a log in one table for querying and then a history like Breck's example as well?

[VolkerBarth]

No, we just use one "history table" for each relevant table - and log the whole "old" contents there. We do not log each change on a column-by column base (and neither does Breck's sample, IIRC).

And we don't log the current contents there - it's in the main table, and we don't log DELETEs either. - It's basically used to be able to monitor the "development of each row" and not used to "audit who did what"... - primarily as the contents is filled through data import routines and not through manual data entry.

---

The suggestions (i.e. after the first horizontal line) are not something we have implemented. They are just some ideas how to implement a "change log" on a column-by-column base. Obviously, they allow a fine-grain check on what was changed but make it difficult to write a query to re-build the "old" and "new" contents on a per-row base. - If you need both facilities, then I guess it would be easiest to monitor both complete row changes (as in Breck's sample) and individual column changes (as in my suggestions). - If you really need to:)

• Or you would only use a "row-change" monitoring (as in Breck's sample) and use a bunch of (non-trivial) UNIONed "column comparison" queries (fine with EXCEPT) to look which columns exactly are different between two row versions. Cf. some of the methods tagged with "except"...