On 14 Jan 2011, at 16:53, Nicolas Williams wrote: > On Fri, Jan 14, 2011 at 10:20:22AM +0000, Philip Graham Willoughby wrote: >> You can do slightly better using a SELECT instead of an UPDATE in the >> trigger body. Install a custom function called rowid_changed() and >> SELECT the result of that; e.g. >> >> CREATE TEMP TRIGGER a AFTER UPDATE ON t FOR EACH ROW BEGIN SELECT >> rowid_changed(OLD.rowid,NEW.rowid) WHERE OLD.rowid != NEW.rowid; END; >> >> You then put code to do whatever your application needs to do when the >> rowid changes in the rowid_changed function you install. You don't >> need to create a new table (removing a small risk of name collision) >> and you will not be doing unnecessary work by polling said table for >> changes. > > But that function would have to track those changed rowids somewhere. > The trigger I posted does exactly that, using SQLite3's own primitives > (a temp table in this case): > > CREATE TEMP TRIGGER t2 AFTER UPDATE ON t FOR EACH ROW BEGIN > INSERT INTO t1 (a, newa) SELECT OLD.rowid, NEW.rowid WHERE > OLD.rowid != NEW.rowid; END; > > No need to write a function in any language.
Which is better depends what you want to track the changed rowids for - my solution is better if you are not interested in the history of rowid changes and want to do something whenever a rowid is changed. If you did want to keep a history and didn't want to be notified when they changed the temporary table is indeed better. If you want both, you can of course do both the select and insert in a single trigger. Since the OP is writing a utility program and wants to keep the GUI cursor over the same row/column even if the rowid change relocates the row in the GUI, I suspect he would be better off using the select and custom function method. Best Regards, Phil Willoughby -- Managing Director, StrawberryCat Limited StrawberryCat Limited is registered in England and Wales with Company No. 7234809. The registered office address of StrawberryCat Limited is: 107 Morgan Le Fay Drive Eastleigh SO53 4JH _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
