ACD Output
The ACD target table contains 5 columns (CNX_ACD_TXID, CNX_ACD_SequenceNumber, CNX_ACD_TimeCreated, CNX_ACD_ChangeUser, and CNX_ACD_ChangeType) followed by the columns from the source table. The ACD target table is a transaction log specific to the source table specified in the ACD replication.
The following examples will use a source table with two character columns, col1 and col2. Col1 is the Primary Key:
The target ACD table has the CNX_ACD columns followed by col1 and col2:
If the following SQL is executed against the source table:
Begin Transaction;
INSERT INTO sourceTable3 (col1, col2) VALUES('abc', 'def');
INSERT INTO sourceTable3 (col1, col2) VALUES('ghi', 'jkl');
Commit;
The ACD target table will contain:
Each row represents a log entry for each operation. Since both inserts were done within the same transaction, they have the same TXID. The Sequence number represents the order within the transaction the operations were executed; i.e. the row with the value 'ab' in col1 was inserted first and has a SequenceNumber of 1.
The TimeCreated column is the timestamp that represents the time the SQL statement was executed.
The ChangeUser column represents the user who executed the query.
The ChangeType column indicates the type of change. A type value of A represents Insert.
Issuing the update statement:
UPDATE sourceTable3 set col2 = 'xxx' where col1 = 'ab';
will result in the following entries being added to the ACD target:
Lines 3 and 4 represent the update statement. Notice that an update results in two rows being added to the ACD table; a B record and a C record. The B record contains data for the Primary Key (or Unique Index) before the update occurred and <null> for the rest of the fields in the record. The C record contains the entire post update record. In this example, col2 was updated so the key information is the same for both the B and C record.
If the key field is updated, the output depends on the way the source database manages key field updates. For example, SQL Server 2012 issues a delete followed by an insert in its transaction log when a key field is updated. This will result in a D record followed by an A record being added to the ACD table. SQL Server 2014 issues an update in this situation. This would result in a B field that contains the original value for the key followed by a C record that contains the entire post update record.
Deleting a record results in a D record being generated. A D record contains the key value for the record that was deleted and <null> in the rest of the columns.
DELETE from sourceTable3 where col1 = 'ab';
Will result in:
Records in the ACD table are only inserted. InstantdbSync will not delete them. Once a set of records has been processed by the application consuming this information, it should delete the processed records if desired.