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
