I have the Audit trail working on several tables in the database, and it saves the updates to a file in the Stores. I have to frequently save the updates so the updates file is not too large otherwise it consumed all of the computer's memory when I try and build the table from the Updates. By keeping the Updates file to 500k or less, it works fine.

When I want to examine any of the Update files I use:

create table _audit_xyz from Updates "AuditTrail_xyz" in store AuditTrail;

1) Why does the _audit_xyz table have 2 rows for every updated row?

The first row has values for the columns TableName, UpdateType, UpdateTimeStamp, Manifest and KeyData but the RowData column is set to NULL. The second row has all of the column values set to NULL except for RowData which of course contains the values that were changed.  Why can't RowData be stored in the first of the two rows?

As it stands, it looks like I will have to write a script to go through the _audit_xyz table and merge the RowData to the first of the two rows so I can search on the KeyData or RowData values and get back useful results.

I find it odd to have a pair of rows when one row should work just as well. What am I missing here?

TIA

Barry

On 3/8/2015 5:48 PM, Barry wrote:
> 1) Why does the _audit_xyz table have 2 rows for every updated row?

Not seeing it here - did a quick test and see a single row for all
operations :
- for delete the KeyData is filled in and RowData is null
- for insert the KeyData is null and Rowdata is filled in
- for update both are filled in

What's the updatetype for the rows you have doubled up ?

How exactly are you adding these rows would be my other question -
database publishing tracks all the changes so is there any chance you're
creating a new row with primary key only and then insert data as
separate operation and commit ?

That would result in 1 row with insert operation (and possibly null
RowData if only primary index column changed) and then another with
update operation that has all the non-primary index fields data.

Raul

Raul wrote:

On 3/8/2015 5:48 PM, Barry wrote:
>> 1) Why does the _audit_xyz table have 2 rows for every updated row?

>
Not seeing it here - did a quick test and see a single row for all
operations :
- for delete the KeyData is filled in and RowData is null
- for insert the KeyData is null and Rowdata is filled in
- for update both are filled in

What's the updatetype for the rows you have doubled up ?
<
It looks like this:

          TableName    UpdateType   UpdateTimeStamp ...    KeyData                     RowData
Row1:  <TableName>     Update        <timestamp>           <PrimaryKeyValues>        NULL
Row2:    NULL              NULL           NULL                      NULL                           <ValuesChanged>

and Row3 & Row4 will be for another row that was updated or inserted in the table with a different <timestamp>, <PrimaryKeyValues> and <ValuesChanged>.

All of the rows in all of the update tables are paired. They all have UpdateType="Update" or NULL. There are no rows with "Insert" or "Delete".

>How exactly are you adding these rows would be my other question - <

I have written a TSyncTables class in Delphi that compares rows between two tables and updates the destination table if necessary. The destination TDataset was assigned a TEDBTable and I use the Insert or Edit methods to add or update the row then post it.

>database publishing tracks all the changes so is there any chance you're
creating a new row with primary key only and then insert data as
separate operation and commit ?<
Not possible because the table's Before Insert/Update trigger will throw an exception if some of the columns are NULL (these columns are not part of the primary key).

>That would result in 1 row with insert operation (and possibly null
RowData if only primary index column changed) and then another with
update operation that has all the non-primary index fields data.<

I agree. Except the first row should have something in the RowData column when the row is first added, and the second row should have all of the columns filled in since it is being updated. But as you can see from the sample above, that's not happening.

Maybe there is something wrong with the steps I'm using to create the files in the Store?

1) When my application starts up and before it opens any tables I execute:
       Publish Database "MyDatabase"  Tables Table1,Table2,Table3;
2)  Then later on ... TSyncTable will update tables ...
3)  Save Updates for Database "MyDatabase" as "AuditFile_x" TO Store "AuditTrail" Tables Table1,Table2,Table3 Compression 7

Step 2 & 3 are repeated because I am syncing a portion of the Destination table (TEDBTable) at a time to a set of CSV files that I've read into a TkbmMemTable (source table). So one AuditTrail_x file is created for each time a set of rows (about 10,000 rows) are sync'd.

....
Later on when all of the tables have been sync'd and a few dozen Store files created, I create the audit tables.
(Right now I am creating separate _Audit_x tables but later on I will probably create temporary tables and merge them into one large audit table).

4) Set Files Store To "AuditTrail"; (so I can access the Configuration.Files table)

Loop through Configuration.Files Table and creates an _Audit_x table for each file in the store "AuditTrail".
5)  Create Table _Audit_x from Updates "AuditFile_x" in Store AuditTrail;
 
Every  _Audit_x table that is created has thousands of paired rows as described in the sample above. Even my stored procedure that uses a sensitive cursor will create pairs of rows when it updates or inserts rows to the table.

Weird, eh?

Tomorrow I might blow away all of the data in the destination tables and see what gets logged when it is only adding rows.

Barry

On 3/9/2015 2:28 AM, Barry wrote:
>             TableName    UpdateType   UpdateTimeStamp ...    KeyData                     RowData
> Row1:  <TableName>     Update        <timestamp>           <PrimaryKeyValues>        NULL
> Row2:    NULL              NULL           NULL                      NULL                           <ValuesChanged>

This looks weird to me - Row 2 obviously cannot be linked to anything
existing as it stands (other than all NULLS it looks little like insert
otherwise but obviously i cannot see how it would be useable with even
tablename missing) !?

And as per Row1 are other rows are updates (no inserts at all) !?

What version of EDB are you using ? (my test was with 2.18 b3).

Did a quick search thru incidents as well and nothing obvious jumps out
- there were some fixes in 2.17 and 2.18 to deal with circular
references and computed columns but none of those seem to apply here.


> I have written a TSyncTables class in Delphi that compares rows between two tables and updates the destination table if necessary. The destination TDataset was assigned a TEDBTable and I use the Insert or Edit methods to add or update the row then post it.

Sounds straightforward.

> 1) When my application starts up and before it opens any tables I execute:
>          Publish Database "MyDatabase"  Tables Table1,Table2,Table3;

You only need to do this once overall (unless you unpublish at some
point) but as far as i know once it's published it's a no-op so no harm
doing that.

> 2)  Then later on ... TSyncTable will update tables ...
> 3)  Save Updates for Database "MyDatabase" as "AuditFile_x" TO Store "AuditTrail" Tables Table1,Table2,Table3 Compression 7
> Step 2 & 3 are repeated because I am syncing a portion of the Destination table (TEDBTable) at a time to a set of CSV files that I've read into a TkbmMemTable (source table). So one AuditTrail_x file is created for each time a set of rows (about 10,000 rows) are sync'd.
> Later on when all of the tables have been sync'd and a few dozen Store files created, I create the audit tables.
> (Right now I am creating separate _Audit_x tables but later on I will probably create temporary tables and merge them into one large audit table).
> 4) Set Files Store To "AuditTrail"; (so I can access the Configuration.Files table)
> Loop through Configuration.Files Table and creates an _Audit_x table for each file in the store "AuditTrail".
> 5)  Create Table _Audit_x from Updates "AuditFile_x" in Store AuditTrail;

Sounds good to me. Other than me using the default EDB naming (that it
generates) my steps were similar (though step 3 lot simpler with some
basic table operations).

> Weird, eh?

Very.

Are you setup to try to load the updates ("load updates" command) - i
wonder if that would work.

However that would likely not be that flexible for what you need this
for (audit trail utility) so definitely get a case opened with elevate
support and have them take a look.


Raul

Raul wrote:

On 3/9/2015 2:28 AM, Barry wrote:
>>             TableName    UpdateType   UpdateTimeStamp ...    KeyData                     RowData
>> Row1:  <TableName>     Update        <timestamp>           <PrimaryKeyValues>        NULL
>> Row2:    NULL              NULL           NULL                      NULL                           <ValuesChanged>

>This looks weird to me - Row 2 obviously cannot be linked to anything
>existing as it stands (other than all NULLS it looks little like insert
>otherwise but obviously i cannot see how it would be useable with even
>tablename missing) !?

Yes, that's what has me confused.

>And as per Row1 are other rows are updates (no inserts at all) !?

There should be a few inserts at the end. But again, I see only pairs of rows throughout the tables.

>What version of EDB are you using ? (my test was with 2.18 b3).

v2.13B2 Unicode C/S

>Did a quick search thru incidents as well and nothing obvious jumps out
>- there were some fixes in 2.17 and 2.18 to deal with circular
>references and computed columns but none of those seem to apply here.


> >I have written a TSyncTables class in Delphi that compares rows between two tables and updates the >>destination table if necessary. The destination TDataset was assigned a TEDBTable and I use the Insert or
>>Edit methods to add or update the row then post it.

>Sounds straightforward.

>> 1) When my application starts up and before it opens any tables I execute:
>>         Publish Database "MyDatabase"  Tables Table1,Table2,Table3;

>You only need to do this once overall (unless you unpublish at some
>point) but as far as i know once it's published it's a no-op so no harm
>doing that.

Yes, I unpublish at the end of my program. I'm only doing this to debug my program so it does not have to be on all the time.

>Sounds good to me. Other than me using the default EDB naming (that it
>generates) my steps were similar (though step 3 lot simpler with some
>basic table operations).

>> Weird, eh?

>Very.

>Are you setup to try to load the updates ("load updates" command) - i
>wonder if that would work.

I suppose as a last resort.

>However that would likely not be that flexible for what you need this
>for (audit trail utility) so definitely get a case opened with elevate
>support and have them take a look.

I am going to try and duplicate the problem in a sample database and see if it still generates row pairs in the Updates table.

Thanks again for your feedback. I'll let you know how it turns out.

Barry