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]
-----------------------------------------------------------------------------
