BareFeet wrote: > create trigger [Insert Orders Refunds Joined] > instead of insert > on [Orders Refunds Joined] > for each row > begin > insert into [Orders Refunds] > ( > ID > , [Order ID] > , Date > , Reason > ) > select > new.ID > , new.[Order ID] > , julianday( new.Date, 'utc' ) -- convert date from string in > localtime to real > , new.Reason > ; > insert into [Orders Refunds Paying] > ( > ID > , Paying > ) > select > last_insert_rowid() > , ( select ID from Paying where Label = new.Paying ) > where new.Paying not null > ; > insert into [Orders Refunds Amount] > ( > ID > , Amount > ) > select > last_insert_rowid() > , new.Amount > where new.Amount not null > ; > end > ; > > > So, is this the best way to tackle the objective of having updatable > views? Or is there a better way? Are there any bugs in my approach? > Any feedback appreciated. >
Tom, This is a very nice set of triggers to handle the base tables of a view (except for all the non-standard MS style quoting of identifiers using square brackets :-)). I believe this is the best way to handle this. The only problem I see is with your use of the last_insert_rowid() function in the instead of insert trigger. 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. When you call it again to insert into "Orders Refunds Amount" you will get the wrong value (i.e. it is no longer the value of the ID column in the newly inserted row in "Orders Refunds" table). You need to call last_insert_rowid() once and save the result for use in both the subsequent inserts. Since you can't use a create table statement in a trigger, 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. Then you update and query that table to get the ids used to link the joined tables to the new record. create temp table "New Id" ( "table name" text primary key, id integer ); insert into "New Id" values ('Orders Refunds', null); create trigger [Insert Orders Refunds Joined] instead of insert on [Orders Refunds Joined] for each row begin insert into [Orders Refunds] ( ID , [Order ID] , Date , Reason ) select new.ID , new.[Order ID] , julianday( new.Date, 'utc' ) -- convert date from string in localtime to real , new.Reason ; update "New Id" set id = last_insert_rowid() where "table name" = 'Orders Refunds'; insert into [Orders Refunds Paying] ( ID , Paying ) select ( select id from "New Id" where "table name" = 'Orders Refunds' ) , ( select ID from Paying where Label = new.Paying ) where new.Paying not null ; insert into [Orders Refunds Amount] ( ID , Amount ) select ( select id from "New Id" where "table name" = 'Orders Refunds' ) , new.Amount where new.Amount not null ; end ; HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users