> 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. > 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. 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... Thank you guys for all of your suggestions. I'll see what I can do about it. Regards, Alex
