Re: Logging database changes

Hi Dawn,

I use a "changes" file with a nask key and fields containing record-
id, user, date, time, port, field.name, old.value, & new.value, all
updated from a trigger on the master file. Field.name, old.value and
new.value are multi-valued, old.value and new.value are oconv'd before
being loaded in the changes record.

I have a generic trigger subroutine that I clone & modify for each new
file I want to track changes for, and use standard names: e.g. for the
customers file the trigger bp is save.changes.customers, and the
"changes" file is named customers.changes. All the xxx.changes files
have the same dictionary structure.

(Note that on most of the systems I support I try to name files as a
plural noun, and the key is the singular noun, i.e. list customers
customer name addr city st zip, etc.)

The bp save.changes.xxx process uses dim() arrays and equate lists of
field.names and oconvs to manage which fields to log changes for. This
is the only real drawback, as those equate lists must be managed
manually and the save.changes.xxx program re-compiled when new fields
are added to a file, or when I want to change which fields are being
tracked, e.g. I don't usually track changes to fields that change
frequently, like customer balance, etc.

An index on the record-id (e.g. the customer number) allows pulling up
all changes for a customer record quickly. You can list them by date
or by-exp field.name using a standard executed "sort" statement.

I have a generic purge routine that prompts for the changes file-name,
and then prompts for either a date and/or a field.name to purge. In
general though, I try to keep many years of changes on file (sometimes
100's of millions of records).

In general this scheme is easy to implement, easy to use, and seems to
have minimal overhead. And being able to track all changes ever made
to a customer's address, or the commission rate, etc, has saved my
bacon many many times.

/Scott Ballinger
Pareto Corporation
Edmonds WA USA
206 713 6006
.

Relevant Pages

  • Re: Handle Triggers in MS Access 2003 with SQL Server as Back-End
    ... I have a trigger created for Customer table. ... Check if there is already a record in the NewCustTracker ... DECLARE @userName nvarchar; ...
    (comp.databases.ms-access)
  • Re: Logging deletes...
    ... when the customer record is deleted, the trigger is fired and then the row ... is added to the delcustomer table it would automatically add the datetime ... Here is my current trigger: ... > Columnist, SQL Server Professional ...
    (microsoft.public.sqlserver.programming)
  • Re: How to generate a merge conflict in a trigger ?
    ... The error that I raise in the trigger happends when I insert a record in the ... Subscriber with a "customer friendly identification" and before it ... CFI, having both rows actives. ...
    (microsoft.public.sqlserver.replication)
  • How to generate a merge conflict in a trigger ?
    ... I would like to be able to generate a conflict for merge replication inside ... And I've a TRIGGER to avoid that (although the application still controls ... You are trying to have two "customer ...
    (microsoft.public.sqlserver.replication)