Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 5 of 5 total |
Why does Updates table need 2 rows to log every row update? |
Sun, Mar 8 2015 5:48 PM | Permanent Link |
Barry | 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 |
Sun, Mar 8 2015 9:42 PM | Permanent Link |
Raul Team Elevate | 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 |
Mon, Mar 9 2015 2:28 AM | Permanent Link |
Barry | 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 |
Mon, Mar 9 2015 8:47 AM | Permanent Link |
Raul Team Elevate | 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 |
Mon, Mar 9 2015 1:02 PM | Permanent Link |
Barry | 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 |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |