On Tue, 21 Sep 2004, Alex wrote: >> 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. > >Well, I'd prefer the 'ON DELETE' trigger to be called every time a deletion >occures regardless of whether it's caused by a "DELETE" or "REPLACE" >statement.
Existing ticket #368 may cover this behaviour. I guess Ron is the user who added the latest remark: http://www.sqlite.org/cvstrac/tktview?tn=368 If I have time at the weekend, I might have a bash at producing a patch that will implement what is required. No promises, mind. What would be preferred? Convert the trigger to an update trigger, or fire a delete trigger followed by an insert trigger? I'd prefer the former (update) as requested in the ticket. I think it may be easier to code, as well, but not sure. > >> 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. > >I'm afraid, it's impossible. The actual SQL code will be written by >different persons, and they must be able to use all the SQL features >supported in SQLite. I actually meant behind the scenes in SQLite. > >Probably, I should provide some more info on the project I'm working on. >There exists a legacy db which does not support SQL syntax at all. The goal >of the project is to allow SQL management for this db. SQLite was chosen as >the SQL interface provider, but the problem is that the old db contains >quite big blob objects (up to 25 MB and more) which cannot be simply copied >into a SQLite table. The obvious solution is to copy not the blob itself, >but only a reference to it (this is what the SecDbKey field actually is). In >this case any changes in an SQLite table must be properly reflected in the >old db. What is worse, the problem of maintaining the coherency is highly >desirable to be solved in the most common manner, since there is no way for >me to predict the structure of SQLite tables ever created or SQL queries >ever constructed. Triggers would be the right solution, but they don't seem >to be accurate enough (at least, the INSERT one). Still thinking what to >do... What is stopping you checking for an existing row in the trigger body yourself? Something like (based on your example): create trigger table_replace_check before insert on 'table' for each row begin insert into table_log select 'D', id, SecDbKey from 'table' where id=NEW.id; end; Then you just keep a regular post insert trigger to put the new updated values into the log. > >Thank you guys for all of your suggestions. I'll see what I can do about it. > >Regards, >Alex > Christian -- /"\ \ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
