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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
