To be totally correct you should use the rowid to perform the update on the
underlying table. The creation of the view to implement the cursor together
with the instead-of trigger to update the underlying table based on the rowid
then becomes a generic pattern to implement a "where current of cursor" style
update. The trigger could then be made generic enough (if necessary) that it
would work for updating any column or combination of columns through the cursor
based on any selection of cursor rows ... and all referential constraints would
still be maintained.
create table numbers (num int unique);
create table others (a int, num int unique references numbers (num) on update
cascade);
insert into numbers values (1), (2), (3), (4);
insert into others values (1,1), (2,2), (3,3), (4,4);
create view updatenumbers
as
select numbers.rowid, *
from numbers
order by num desc;
create trigger updnum instead of update of num on updatenumbers
begin
update numbers
set num = new.num
where rowid=old.rowid;
end;
update updatenumbers set num = num + 1;
select * from numbers;
select * from others;
2
3
4
5
1|2
2|3
3|4
4|5
---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
