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_name text,
id integer
);
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 (
id integer
);
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]
-----------------------------------------------------------------------------
