Back to Community Threads
1
What are the best practices for using the audit and auditdet tables for CDC?
Question asked by Stan Paulauskas - 3/18/2022 at 7:44 AM
Answered
I'm working on a CDC for an ETL process that pumps data from the Atrex database into an EDW staging area. The idea is to use the audit and auditdet tables to only pull the changed information of interest and update that information in the EDW. I have a few questions about the audit and auditdet tables. 

  1. Is it possible to join the audit table with the auditdet table? I don't immediately see a correlation between the two tables.
  2. In the auditdet table, I'm assuming that the "Reference" field uses the PK for the changed table. Is that correct?

4 Replies

Reply to Thread
0
Terry Swiers Replied
3/18/2022 at 2:07 PM
Employee Post Marked As Answer
The audit and auditdet tables are not linked.  The audit table contains high level information about which function was started/completed, from where, and when.  

The AuditDet table contains field level changes from a few specific fields on tables to allow some research into things that generally involve "money" such as inventory item quantity, cost and customer name or pricing level changes.   For the code and cust tables, "Reference" is the PK for the table.  For the serial table, it's only the serial number, which is only half of the primary key".
0
Stan Paulauskas Replied
3/18/2022 at 2:18 PM
Gotcha! A followup question: Do all changes from the UI get populated into the auditdet table?
1
Terry Swiers Replied
3/18/2022 at 2:51 PM
Employee Post
Just the following specific fields:

code
  OnHand
  Cost
  NoDeplete 
  Serialized
  EHFInd

cust 
  Company
  LastName
  MasterCust
  Pricing
  DiscPct
  MaxCredit
  SalesRep

serial
  Cost
  StockCode
  SerialNum
  TransNum
0
Stan Paulauskas Replied
3/18/2022 at 3:50 PM
Awesome! Thank you very much for the insights.
Back to Community Threads

Reply to Thread