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.

Robert

Robert


Last time I did something like that was c1985 when I was young and naive and my word of wisdom is - don't -

On a more serious note find out why. It may be that they think this is a solution to a problem and they are wrong.

Roy Lambert

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:0D7EDA32-99DD-4249-9F6A-6BDEAA233A5D@news.elevatesoft.com...
>I have a customer that wants to log EVERY change to a table. They want
>before and after values on each changed field.
>

What a pain its doable but the customer is never right.
Assume lots of tables ?
Who will have rights to the log ?
Even the data manager can cover his/her tracks.
A couple of new fields on each table,

say -- RecordChangedAt.DateTimeStamp
next -- ByWhom.Changed.String

That info will need to go to another identical table
or someone else will come along and u will never find
what the 1st guy wipes out.

Text log files dont stand a chance, Carbon copies of
all the tables is the only real way to go. I know coz I
had to redo my booking screen to find out who deleted
some calls taxi firms never showed up for. After I put
that in by altering all the booking's code the customer
wanted it on over 200 other tables, I told him to get
lost but then I had that sort of relationship with most of
my customers "Erin Brokovitch there called boobs Ed."
Good luck tho I will think off u while I have my feet up.

Rita

Roy Lambert wrote:

> Robert
>
>
> Last time I did something like that was c1985 when I was young and
> naive and my word of wisdom is - don't -
>
> On a more serious note find out why. It may be that they think this
> is a solution to a problem and they are wrong.
>
> Roy Lambert

Or on the contrary, it can be a useful tool to find out what when
wrong, "The reason that so-and-so stopped getting email statements was
that Joe deleted the email address and he did it on 12 January 2009 at
12:15 pm".

I agree with other comments at this is pretty useless from a security
point of view (though most of our users don't even know it is there and
if they did they'd have no idea how to fiddle it) - but for training
and support it is potentially invaluable.  You can also gain insights
into how much maintenance is done on tables etc.  - you can tell when
the log table swells

We do it on master tables only - we have a separate audit trail for
transactions.

I'll post my code below - it called from the BeforePost and
BeforeDelete of each table you want to log.

The gurus will instantly realise that any changes done by SQL will not
be logged automatically.  I have avoided this problem by avoiding SQL
changes to master tables - if I *had to* do SQL changes, then I guess
I'd create the log entries at the time.


Cheers

Jeff

---------------
I've used a few of my own functions, but should be self explanatory.

/* SQL-92 Table Creation Script with DBISAM Extensions */

CREATE TABLE IF NOT EXISTS "Log"
(
  "Type" VARCHAR(16),
  "LoggedAt" TIMESTAMP,
  "ComputerName" VARCHAR(40),
  "UserName" VARCHAR(40),
  "TableName" VARCHAR(20),
  "LogDetail" BLOB,
NOCASE PRIMARY KEY ("LoggedAt","ComputerName","UserName","Type")
COMPRESS FULL
LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 1
);

CREATE NOCASE INDEX IF NOT EXISTS "idxTypeTable" ON "Log"
("Type","TableName","LoggedAt");


procedure TCommon.Log(EventText: string; TableName: string = ''; sType:
string = '');
begin
 if apmDM.LogTable.Exists then
 begin
   apmDM.OpenTable(apmDM.LogTable);
   apmDM.LogTable.Insert;
   apmDM.LogTableType.AsString := sType;
   apmDM.LogTableLoggedAt.AsDateTime := Now;
   apmDM.LogTableComputerName.AsString := ComputerName;
   apmDM.LogTableUserName.AsString := UserName;
   apmDM.LogTableTableName.AsString := TableName;
   apmDM.LogTableLogDetail.AsString := EventText;
   try
     apmDM.LogTable.Post;
   except
     Sleep(1000);
     apmDM.LogTableLoggedAt.AsDateTime := Now;// Avoid duplicate keys
     apmDM.LogTable.Post;
   end;
 end;
end;

procedure TCommon.LogTableChange(Table: TDBISAMTable);
var
 s, s1, s2, s3: string;
 i: integer;
 img: TImage;
 bImage: Boolean;
begin
 if Table.State = dsInsert then s3 := 'Table Insert'
 else if Table.State = dsEdit then s3 := 'Table Change'
 else s3 := '';
 s := '';
 with Table do
   for i := 0 to FieldCount - 1 do
   begin
     if (Fields[i].FieldKind = fkData)
       and ((i < 4) or (Fields[i].Value <> Fields[i].OldValue)) then
     begin
       bImage := False;
       if Fields[i].IsBlob then
       begin
         img := TImage.Create(Self);
         try
           if DBPhotos.PhotoFieldToImage((Fields[i] as TBlobField),
img) then
             bImage := True;
         finally
           img.Free;
         end;
       end;
       if bImage then
       begin
         if (Fields[i].Value <> Fields[i].OldValue)
           or (Table.State = dsInsert) then
           s := s + Fields[i].FieldName + ': New Image'#13#10
         else s := s + Fields[i].FieldName + ': Unchanged Image'#13#10;
       end
       else begin
         try
           s1 := Fields[i].OldValue;
         except
           s1 := '(null)';
         end;
         try
           s2 := Fields[i].Value;
         except
           s2 := '(null)';
         end;
         if (Fields[i].Value <> Fields[i].OldValue) then
           s := s + Fields[i].FieldName + ': ' + s1 + ' => ' + s2 +
#13#10
         else s := s + Fields[i].FieldName + ': ' + s2 + #13#10;
       end;
     end;
   end;
 if s <> '' then Common.Log(s, Table.TableName, s3);
end;

procedure TCommon.LogTableDelete(Table: TDBISAMTable);
var
 s, s2: string;
 i: integer;
begin
 s := '';
 with Table do
   for i := 0 to FieldCount - 1 do
   begin
     if (Fields[i].FieldKind = fkData) then
     begin
       try
         s2 := Fields[i].Value;
       except
         s2 := '(null)';
       end;
       s := s + Fields[i].FieldName + ': ' + s2 + #13#10;
     end;
   end;
 if s <> '' then Common.Log(s, Table.TableName, 'Table Delete');
end;

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz
+
Joan and Jeff Cook
The Cooks Oasis
www.cookislandsoasis.com

"Jeff Cook" <jeffc@aspect.co.nz> wrote in message
news:4FD16FAA-CD9E-472F-8031-9D74E59FF5D6@news.elevatesoft.com...
> Roy Lambert wrote:
>

Thanks a lot for the reply and the code. This might work well for the
application in question, since we already have a transaction log and I
believe all changes to "master" tables are made using tTables.

Why the test for 4 when looping thru the tables? I assume it is some unique
issue with your table layout. Please explain.

Have you ever cosidered using the persistent field DisplayLabel instead of
the field name?

Robert

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:3E801349-819F-4D7F-957B-87821A9CF56D@news.elevatesoft.com...
> Robert
>
>
> Last time I did something like that was c1985 when I was young and naive
> and my word of wisdom is - don't -
>
> On a more serious note find out why. It may be that they think this is a
> solution to a problem and they are wrong.
>

I hear you, but if it needs to be done, then I need to have a plan for doing
it.

Robert

"Rita" <no@spam.com> wrote in message
news:88DAD738-5D87-4A5B-8990-491B343E4080@news.elevatesoft.com...
>
> "Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
> news:0D7EDA32-99DD-4249-9F6A-6BDEAA233A5D@news.elevatesoft.com...
>>I have a customer that wants to log EVERY change to a table. They want
>>before and after values on each changed field.
>>
>
> What a pain its doable but the customer is never right.

They do write the checks, a not totally inconsecuential matter.

Robert

Hi Robert
>
<snip>
>
> Why the test for 4 when looping thru the tables? I assume it is some
> unique issue with your table layout. Please explain.
>

We assume that the first 4 columns in a table are enough to uniquely
identify the row that has been changed, so we log the contents of those
whether changed or not.

> Have you ever cosidered using the persistent field DisplayLabel
> instead of the field name?
>
No!  Should I?  

Cheers

Jeff


--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz
+
Joan and Jeff Cook
The Cooks Oasis
www.cookislandsoasis.com

"Jeff Cook" <jeffc@aspect.co.nz> wrote in message
news:6A804A26-62D0-476D-9EDB-4074B6EAFC0B@news.elevatesoft.com...
>
>> Have you ever cosidered using the persistent field DisplayLabel
>> instead of the field name?
>>
> No!  Should I?
>

Don't know in your case. For my databases, it would make the log much more
readable, since the field names would be the the same as the column
descriptions that users see in the grids, etc. My field names have a method
to the madness, but they are not very descriptive to a user.

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.

Robert, I just did this as a custom change for a client that licenses our
source code. They have the SQL Server version of our accounting software and
wanted it done via triggers, which works well... no messing about with the
application (other than the form that creates the database tables and
triggers, etc.), and the code runs on the server.

If ElevateDB supports triggers I would suggest doing it that way rather than
via the BeforePost and BeforeDelete events.


--
Trevor Davis
Davis Business Systems Ltd.
www.dbsonline.com
BS1 Accounting, Time Billing, Distribution, and Manufacturing Software
Delphi Source Code (royalty-free)
BS1 Professional Time Billing and Accounting is free for Delphi and
C++Builder programmers