Hi All (especially Dennis I guess ;-) ) Focusing on the "instead of update" trigger in my example (copied below), you'll notice that the trigger scans through the table [Orders Refunds Amount] three times to find the matching ID, once for each of insert, delete, update, whose where tests contain the following (partial):
insert: where new.ID not in ( select ID from [Orders Refunds Amount] ) delete: where [Orders Refunds Amount].ID = new.ID update: where [Orders Refunds Amount].ID = new.ID When you look at the complete where clauses in each, you'll realize that, by design, only one of the where clauses is ever true. So it seems inefficient for the trigger to test three times. Is there a more efficient method? Quoting the relevant section of my original post: > Now for updating the Paying and Amount fields. Again, instead of the > view, I want to update the corresponding joined table for each. > Paying has to convert a text label to the related integer value. > Because the [Orders Refunds Paying] and [Orders Refunds Amount] > tables are designed to only contain a row if their value is not > null, I have to: > > 1. Insert a row if it doesn't already exist for that ID, or > 2. Delete the row if the value has changed to null, or > 3. Simply update the value if the row exists and the new value is > not null. > > So I have one trigger ... which performs either an insert, delete or > update: > create trigger [Update Orders Refunds Joined Amount] > instead of update of Amount > on [Orders Refunds Joined] > for each row > begin > -- insert if row doesn't exist and new value not null > insert into [Orders Refunds Amount] > ( > ID > , Amount > ) > select > new.ID > , new.Amount > where new.Amount not null > and new.ID not in ( select ID from [Orders Refunds Amount] ) > ; > -- delete if row exists and new value is null > delete from [Orders Refunds Amount] > where new.Amount is null > and [Orders Refunds Amount].ID = new.ID > ; > -- update if row exists and new value not null > update [Orders Refunds Amount] > set Amount = new.Amount > where new.Amount not null > and [Orders Refunds Amount].ID = new.ID > ; > end > ; Thanks, Tom BareFeet -- Widest range of Macs & accessories in Australia: http://www.tandb.com.au/forsale/?sig _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
