On 13 Jan 2011, at 17:27, Nicolas Williams wrote: > On Thu, Jan 13, 2011 at 10:59:29AM +0000, Simon Slavin wrote: >> His problem is that he doesn't know which rows are aliased to rowid, >> so he can't provide a list of column names. So the following might be >> closer >> >> CREATE TEMP TRIGGER [UpdatedRowIdTrigger] AFTER UPDATE ON TestTable >> BEGIN >> UPDATE UpdatedRowId Set Value = new.rowid WHERE new.rowid != old.rowid; >> END; >> >> But I don't know whether SQLite can actually do that comparison at that >> stage. > > I just tried it. SQLite3 does handle this correctly:
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. 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
