Robert

> I have a customer that wants to log EVERY change to a table. They want
> before and after values on each changed field.
>
> I assume the idea is to grab the record on before post and loop thru the
> fields. It sounds horrible, but maybe it's the only solution.
>
> Has anybody done something like this? Any tips, examples, wise words of
> advice? Thanks.

I think DBIsam triggers are a good solution.  I use a single change log
table to capture changes on all tables.  If you are using client server you
need to modify the server source.  If it is file based add the triggers to
the client side.

I include below the change log table structure and the code for logging
client side.  All my tables have an autoinc (pk) as the first field which I
use in "KeyValue".

John



CREATE TABLE IF NOT EXISTS "ChangeLog"
(
  "TableName" VARCHAR(32),
  "FieldName" VARCHAR(32),
  "KeyValue" INTEGER,
  "OldValue" MEMO,
  "NewValue" MEMO,
  "ChangeID" GUID,
  "UserId" VARCHAR(20),
  "ChangeDate" TIMESTAMP,
  "Action" VARCHAR(1),
PRIMARY KEY ("RecordID") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
);

unit AuditModuleU;

interface

uses
 Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
 dbisamtb;

type
 TAuditModule = class(TDataModule)
   Engine: TDBISAMEngine;
   procedure EngineAfterDeleteTrigger(Sender: TObject;
     TriggerSession: TDBISAMSession; TriggerDatabase: TDBISAMDatabase;
     const TableName: String; CurrentRecord: TDBISAMRecord);
   procedure EngineAfterInsertTrigger(Sender: TObject;
     TriggerSession: TDBISAMSession; TriggerDatabase: TDBISAMDatabase;
     const TableName: String; CurrentRecord: TDBISAMRecord);
   procedure EngineAfterUpdateTrigger(Sender: TObject;
     TriggerSession: TDBISAMSession; TriggerDatabase: TDBISAMDatabase;
     const TableName: String; CurrentRecord: TDBISAMRecord);
 private
   { Private declarations }
   procedure AuditChanges(Sender: TObject;
                          TriggerSession: TDBISAMSession; TriggerDatabase:
TDBISAMDatabase;
                          const TableName: String; CurrentRecord:
TDBISAMRecord; Action: string);

 public
   { Public declarations }
 end;

var
 AuditModule: TAuditModule;

implementation

{$R *.DFM}
uses
 comobj,Activex;

procedure TAuditModule.AuditChanges(Sender: TObject;
 TriggerSession: TDBISAMSession; TriggerDatabase: TDBISAMDatabase;
 const TableName: String; CurrentRecord: TDBISAMRecord; Action: string);
var
 ChangesQuery:TDBisamQuery;
 i:integer;
 aguid:tguid;
 gs:string;
 keyval:integer;

begin
 if uppercase(TableName) <> 'CHANGELOG' then // Include/exclude tables to
audit
 begin
   ChangesQuery := TDBisamQuery.Create(Self);
   try
     ChangesQuery.SessionName := TriggerSession.SessionName;
     ChangesQuery.DatabaseName := TriggerDatabase.DatabaseName;
     ChangesQuery.SQL.Add('Insert Into Changelog
(TableName,FieldName,KeyValue,OldValue,NewValue,ChangeId,UserId,ChangeDate,A
ction)');
     ChangesQuery.SQL.Add('Values
(:TableName,:fieldname,:KeyValue,:OldValue,:NewValue,:ChangeId,:UserId,curre
nt_timestamp,:Action)');
     ChangesQuery.Prepare;
     OleCheck(CoCreateGuid(aguid));
     gs := guidtostring(aguid);
     if Action <> 'D' then
     begin
       keyval := currentrecord.fields[0].asinteger;
       for i := 0 to currentrecord.FieldCount-1 do
       begin
         if currentrecord.Fields[i].OldValue <>
currentrecord.Fields[i].Value then
         begin
           ChangesQuery.ParamByName('TableName').Asstring := TableName;
           ChangesQuery.ParamByName('FieldName').Asstring :=
currentrecord.Fields[i].FieldName;
           ChangesQuery.ParamByName('KeyValue').Asinteger := keyval;
           if not varisnull(currentrecord.Fields[i].OldValue) then
             ChangesQuery.ParamByName('OldValue').Asstring :=
currentrecord.Fields[i].OldValue
           else
             ChangesQuery.ParamByName('OldValue').Asstring := '';
           if not varisnull(currentrecord.Fields[i].Value) then
             ChangesQuery.ParamByName('NewValue').Asstring :=
currentrecord.Fields[i].Value
           else
             ChangesQuery.ParamByName('NewValue').Asstring := '';
           ChangesQuery.ParamByName('ChangeId').asstring := gs;
           if Engine.EngineType = etserver then
             ChangesQuery.ParamByName('UserId').Asstring :=
TriggerSession.CurrentServerUser
           else
             ChangesQuery.ParamByName('UserId').Asstring := 'John';
//assign user name locally
           ChangesQuery.ParamByName('Action').asstring := Action;
           ChangesQuery.execsql;
         end;
       end;
     end
     else
     begin
       keyval := currentrecord.fields[0].oldvalue;
 // Log only primary key for delete
 {
       ChangesQuery.ParamByName('TableName').Asstring := TableName;
       ChangesQuery.ParamByName('FieldName').Asstring := '';
       ChangesQuery.ParamByName('KeyValue').Asinteger := keyval;
       ChangesQuery.ParamByName('OldValue').Asstring := '';
       ChangesQuery.ParamByName('NewValue').Asstring := '';
       ChangesQuery.ParamByName('ChangeId').asstring := gs;
       if DBISAMEngine1.EngineType = etserver then
         ChangesQuery.ParamByName('UserId').Asstring :=
TriggerSession.CurrentServerUser
       else
         ChangesQuery.ParamByName('UserId').Asstring := 'John'; //assign
user name locally
       ChangesQuery.ParamByName('Action').asstring := Action;
       ChangesQuery.execsql;
 }

 // Log all field values in delete
       for i := 0 to currentrecord.FieldCount-1 do
       begin
         ChangesQuery.ParamByName('TableName').Asstring := TableName;
         ChangesQuery.ParamByName('FieldName').Asstring :=
currentrecord.Fields[i].FieldName;
         ChangesQuery.ParamByName('KeyValue').Asinteger := keyval;
         if not varisnull(currentrecord.Fields[i].OldValue) then
           ChangesQuery.ParamByName('OldValue').Asstring := currentrecord.F
ields[i].OldValue
         else
           ChangesQuery.ParamByName('OldValue').Asstring := '';
         ChangesQuery.ParamByName('NewValue').Asstring := '';
         ChangesQuery.ParamByName('ChangeId').asstring := gs;
         if Engine.EngineType = etserver then
           ChangesQuery.ParamByName('UserId').Asstring :=
TriggerSession.CurrentServerUser
         else
           ChangesQuery.ParamByName('UserId').Asstring := 'John'; //assign
user name locally
         ChangesQuery.ParamByName('Action').asstring := Action;
         ChangesQuery.execsql;
       end;
     end;
     ChangesQuery.Prepare;
   finally
     ChangesQuery.Free;
   end;
 end;
end;

procedure TAuditModule.EngineAfterDeleteTrigger(Sender: TObject;
 TriggerSession: TDBISAMSession; TriggerDatabase: TDBISAMDatabase;
 const TableName: String; CurrentRecord: TDBISAMRecord);
begin

auditchanges(sender,triggersession,triggerdatabase,tablename,currentrecord,'
D');
end;

procedure TAuditModule.EngineAfterInsertTrigger(Sender: TObject;
 TriggerSession: TDBISAMSession; TriggerDatabase: TDBISAMDatabase;
 const TableName: String; CurrentRecord: TDBISAMRecord);
begin

auditchanges(sender,triggersession,triggerdatabase,tablename,currentrecord,'
I');
end;

procedure TAuditModule.EngineAfterUpdateTrigger(Sender: TObject;
 TriggerSession: TDBISAMSession; TriggerDatabase: TDBISAMDatabase;
 const TableName: String; CurrentRecord: TDBISAMRecord);
begin

auditchanges(sender,triggersession,triggerdatabase,tablename,currentrecord,'
U');
end;

end.