Re: [sqlite] last_row_id() after insert via trigger

2007-08-07 Thread Dennis Cote 

T&B wrote:


Short question:

When I explicitly insert a row into a table, I am able to use the 
last_insert_rowid() function to get the rowid of that inserted row.


But how do I get the rowid of a row inserted by a trigger? It seems 
that last_insert_rowid() doesn't change.





Tom,

The short answer is you can't do that. The long answer is that you must 
pass the inserted rowid back from the trigger to its calling context 
explicitly.


The last_insert_rowid function behaves as it does so that mainline code 
is isolated from changes in the body of a trigger. This allows complex 
triggers to insert linked data into multiple tables, which can in turn 
cause other triggers to fire and insert into still other tables (perhaps 
a log or audit trail). These inserts do not effect the rowid seen by the 
first level of trigger, or the mainline code.


Since SQLite's views do not have any special syntax to allow a trigger 
to specify which rowid should be returned as the last inserted rowid 
from the trigger, you have to do this yourself. Create a 
last_inserted_row table that can have a row for each table for which you 
need to track the inserted rowid.


   create table last_inserted_row (
   table_nametext,
   idinteger
   );

Now you can insert data into this table in your trigger, and extract the 
data from this table in your mainline code.


   create trigger if not exists "Insert In View"
   instead of insert on "Test View"
   begin
   insert into "Test Table" ( rowid ) values ( null );
   update last_inserted_row
   set id = last_insert_rowid()
   where table_name = 'Test Table';   
   end;


And

   insert into "Test View" (rowid) values (null);
   select rowid || ' from view after insert' from "Test View"
   where rowid=
   (select id from last_inserted_row
   where table_name = 'Test Table');

Note, this can be simplified if you only need to track one table by 
eliminating all the table name storage and testing as below.


   create table last_inserted_row (
   idinteger
   );

   create trigger if not exists "Insert In View"
   instead of insert on "Test View"
   begin
   insert into "Test Table" ( rowid ) values ( null );
   update last_inserted_row
   set id = last_insert_rowid();
   end;

   insert into "Test View" (rowid) values (null);
   select rowid || ' from view after insert' from "Test View"
   where rowid=(select id from last_inserted_row);
 
HTH

Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] last_row_id() after insert via trigger

2007-07-29 Thread 

Hi all,

Short question:

When I explicitly insert a row into a table, I am able to use the  
last_insert_rowid() function to get the rowid of that inserted row.


But how do I get the rowid of a row inserted by a trigger? It seems  
that last_insert_rowid() doesn't change.


Longer example:

For example, I have a table and a view, with a trigger that will  
insert a row into the table, if the user attempts to insert a row into  
the corresponding view. That works fine, except that I can't get the  
rowid of the inserted row.


See (and test) sql below:

begin transaction;

-- Create the Test Table, View and Trigger:
drop table if exists "Test Table";

create table "Test Table" ( "Dummy" );

create view if not exists "Test View" as select rowid, * from "Test  
Table";


create trigger if not exists "Insert In View"
instead of insert
on "Test View"
begin
insert into "Test Table" ( rowid ) values ( null );
end;

-- Test inserting into the table:
insert into "Test Table" (rowid) values (null);
select rowid || ' from table after insert' from "Test Table" where  
rowid=last_insert_rowid();

insert into "Test Table" (rowid) values (null);
select rowid || ' from table after insert' from "Test Table" where  
rowid=last_insert_rowid();


-- Test inserting into the view, which will trigger insert into the  
table:

insert into "Test View" (rowid) values (null);
select rowid || ' from view after insert' from "Test View" where  
rowid=last_insert_rowid();

insert into "Test View" (rowid) values (null);
select rowid || ' from view after insert' from "Test View" where  
rowid=last_insert_rowid();
select rowid || ' from table' from "Test Table" where  
rowid=last_insert_rowid();


rollback;

which gives output:

1 from table after insert
2 from table after insert
2 from view after insert
2 from view after insert
2 from table

but should increment after each insert, ie give 1, 2, 3, 4, 4

Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-