Hello,
I am inserting several records into a table within a transaction. There is one field in the table that should never have a duplicate value. If an attempt to insert a record that contains a value in that particular field that already exists in another record, then I want to detect this and copy the original record to a history table, then update the original record with the latest data. The table is being loaded from an xml file that can look something like this:
<parent>
<child Code = "12345" Desc = "Some description" />
<child Code = "12346" Desc = "Some description" />
<child Code = "12347" Desc = "Some description" />
<child Code = "12345" Desc = "Some different description" />
</parent>
Given the above XML snippet, I want to end up with:
Item Table:
ItemID CODE DESC
1 "12345" "Some different description"
2 "12346" "Some description"
3 "12347" "Some description"
and:
ItemHistory table:
ItemHistoryID ItemID CODE DESC
1 1 "12345" "Some description"
I do
NOT want to end up with:
Item Table:
ItemID CODE DESC
1 "12345" "Some different description"
2 "12346" "Some description"
3 "12347" "Some description"
4 "12345" "Some description"
So, when I add each record to the Item context, I want to be able to detect if the CODE value already exists and take the appropriate action. However, since the transaction has not been committed, any record having such a duplicate is not available to be queried yet - at least as far I know.
Is there a method to query a context to see what records have been added but not committed yet?
Thanks,
Dennis