Hi Dennis, Thanks for your reply. I really appreciate the feedback.
> This is a very nice set of triggers to handle the base tables of a > view > I believe this is the best way to handle this. Thanks, it's good to at least know that I'm heading the right way. I believe this is the best way to handle this. > (except for all the non-standard MS style quoting of identifiers > using square brackets :-)). Wash your mouth ;-) No, I have no MS legacy or habits. I use the square brackets for identifiers because I find that using double quotes doesn't catch errors. If I say select "column name that does not exist" I get a string back. But if I use square brackets SQLite gives me an error that the column doesn't exist, which is far more useful, especially when embedded in some function. > The only problem I see is with your use of the last_insert_rowid() > function in the instead of insert trigger. Well spotted. > If your new row has non-default values for both the Paying and > Amount fields, you will have a problem. After the insert into "Order > Refunds Paying" the value returned by last_insert_rowid() changes to > the rowid of the newly inserted row in that table. I actually tested that scenario (and others) and it seems to work. It works in this case because all the joined tables have the same rowid for related records. So each insert in the subsequent tables is using the same rowid, so last_insert_rowid() gives the same result after each insert. > You need to call last_insert_rowid() once and save the result for > use in both the subsequent inserts. Yes. I will have to do that if the joined tables don't have the same rowid for related records (which is not the case in this example). > Since you can't use a create table statement in a trigger, Yes, I thought of doing that and realized the limitation of triggers not allowing create temporary table. Is this restriction part of standard SQL, or just SQLite? Is it likely to change? It seems a common need. > you have to create a table to use for this purpose, which you can > update and query in the trigger. This could be a temporary table > that is created and initialized when the database is opened. Oh I see, yes, I guess I could prefix the initiating insert in my program with a create temporary table, but since that would be outside of the SQL, my database would lose portability. That is to say, if I use my database outside of my program, such as via the command line, then inserting into the view will fail with some error like "table 'New ID' doesn't exist". In order to facilitate portability (ie my database will function in any environment, not just when running in my program), I guess I'll have to create a permanent (ie not temporary) table to track the last_row_id of each table. > Thanks, Tom _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
