Alex wrote:
> Hi, Christian
>
> Thanks for your response. The situation, however, is more complicated.
> Please, consider an example in SQL:
>
> CREATE TABLE table (ID text UNIQUE, Name text, Surname text, SecDbKey
> text)
>
> INSERT INTO table VALUES ('001', 'John', 'Smith', 'A0B1C2D3')
>
> INSERT OR REPLACE INTO table VALUES ('001', 'Jane', 'Parker',
> 'E4F51234')
>
> In the given example the "UNIQUE" constraint will cause the row to be
> overwritten, and the "A0B1C2D3" key will be lost. And it seems there
> is no way to intercept this deletion. The ROWID will also be changed,
> so it cannot be used for tracking purposes. If anyone knows a
> solution, please, advice.
>
> Thanks,
> Alex
>
>
>> If there is an "OR REPLACE" clause on your primary key, then you know
>> exactly which row to delete from the secondary db. It will be the
>> one with the same primary key as the new row. So, for each insert,
>> just delete the row from the secondary db with the same key. If it
>> doesn't exist, you'll have lost nothing (a bit of time, perhaps, as
>> you'll have to do a negative index scan.) Then just insert the data
>> as you would normally do.
>>
>>
>>
>> ChristianAlex, Christian's proposal doesn't work for you because you are storing a reference to the secondary DB in the primary DB. If you link your table the other way, by storing the a reference to the primary DB in the corresponding secondary DB record, it will work as he described. You could also check if the insert is going to result in a replacement by doing a select for the primary id before doing the insert. If it exists, then delete the corresponding record from the secondary DB before executing the insert (which you know will be replacing the existing record).
