On Tue, 21 Sep 2004, 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.
What would you be planning to do with this "SecDbKey" column? There's
nothing stopping you retrieving the current value from table.
But I think the gist of you email is that you'd prefer the "INSERT OR
REPLACE" to be handled like an update. I can't see a reason why an OLD
record could be created for replace inserts. The problem then becomes how
do you know in the trigger whether OLD is valid? If the insert is a
straight insert, then OLD will not be valid, and you'll probably get an
exception trying to use it.
In which case it might be better to convert the replace insert into an
update, and use the update trigger which is guaranteed to have valid OLD
and NEW.
But that is not my decision to make. Create a new enhancement ticket in
CVSTrac.
>
>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.
>>
>>
>>
>> Christian
>
>
>
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
