On Fri, Jan 14, 2011 at 10:20:22AM +0000, Philip Graham Willoughby wrote: > On 13 Jan 2011, at 17:27, Nicolas Williams wrote: > > On Thu, Jan 13, 2011 at 10:59:29AM +0000, Simon Slavin wrote: > >> 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.
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. (I do, however, wish that SQLite3 could persist temp tables/triggers such that when one opens a DB handle all those temp tables and triggers get automatically created.) Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
