1) Why on earth would you want to scroll all the way to the bottom of a long email to get the response simply for the sake of "We read English top to bottom."
2) This is going to be a challenge for me because I'm not writing a fixed DB with a known schema. I'm writing a higher-level data store wrapper using SQLITE as the backing. The schema is configured at runtime. 3) I found the following comment on the mysql dev site (relevant since "replace into" is inspired by mysql). They apparently fire the triggers as expected. It seems to me that your comments overcomplicate the requirements. If you are going to delete a row, call the delete triggers....that is all that is needed as best I can tell. However, I do admit that I am not well educated on SQL db engines. I'm figuring all this out as I go along. Feel free to tell me how absurdly wrong I am. http://dev.mysql.com/doc/refman/5.0/en/replace.html > MySQL uses the following algorithm for REPLACE (and LOAD DATA ... > REPLACE): > > Try to insert the new row into the table > > While the insertion fails because a duplicate-key error occurs for a > primary key or unique index: > > Delete from the table the conflicting row that has the duplicate key > value > > Try again to insert the new row into the table > and in the comments... > If you are using REPLACE INTO... triggers are fired in this order > (if delete of duplcate key is used): > - before insert > - before delete > - after delete > - after insert On Jul 6, 2009, at 1:15 PM, Simon Slavin wrote: > Please quote previous text above your response to it. We read English > top to bottom. > > On 6 Jul 2009, at 8:22pm, James Gregurich wrote: > >> On Jul 6, 2009, at 3:53 AM, Simon Slavin wrote: >> >>> It should not call DELETE triggers since it never deletes. It >>> should >>> call either INSERT triggers or UPDATE triggers depending on which >>> one >>> it decides to do. >>> >>> In the meantime, you can do the same thing yourself: instead of >>> calling INSERT OR REPLACE, test to see which one would happen then >>> call either INSERT or UPDATE. The triggers on both of those >>> combinations will work correctly. > >> so you are suggesting that I put an INSERT in a C loop checking for a >> constraint violation failure. > > You have pointed out an error I made. I was thinking that REPLACE > meant that only one existing row could be replaced. This is wrong: > the new row can replace any number of existing rows. Thank you for > spotting my error. The documentation points it out, not quite as > clearly, in the REPLACE section of > > http://www.sqlite.org/lang_conflict.html > > So any proper trigger structure would have to call a combination of > all three types of triggers: INSERT, DELETE and UPDATE. I can't think > of a good way to manage this properly. And that may be why INSERT OR > REPLACE itself doesn't use triggers correctly: it's too complicated to > work out which of the existing rows is being REPLACED. And you can't > call DELETE triggers instead, because they're intended to stop > something being deleted, and it might be okay to DELETE this thing if > you're immediately going to INSERT another row that satisfies the > requirement. > > I can only suggest that you handle the constraints testing yourself, > in your code. You will know, from the design of your database, > whether your new row should be considered a replacement for an > existing one. You're going to have to replace your INSERT OR REPLACE > with your own check for other constraints, and then deciding in your > own code what commands to execute. I don't think it will be possible > to write code to do this properly for an arbitrary database. > >>> Or do the INSERT, allow it to fail if >>> it will, then do the UPDATE. > > This previous suggestion of mine won't work at all, given that there's > no way to tell which of the existing records you think you're > updating. > > I conclude that INSERT OR REPLACE isn't compatible with triggers, or > that triggers have to support FOR EACH TRANSACTION as well as ROW and > STATEMENT, or that INSERT OR REPLACE has to treat a primary key > conflict differently to some other kind of conflict. There are > drawbacks to all three of these. I can't think of a way to do it that > suits SQLite's small/neat/obvious design criteria. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
