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: sqlite> CREATE TEMP TABLE t(a integer primary key, b text); sqlite> INSERT INTO t VALUES (1, 'abc'); sqlite> INSERT INTO t VALUES (2, 'def'); sqlite> CREATE TEMP TABLE t1 (a integer, newa integer); sqlite> 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; sqlite> UPDATE t SET b = 'xyz' WHERE a = 1; sqlite> SELECT * FROM t; 1|XYZ 2|def sqlite> SELECT * FROM t1; sqlite> UPDATE t SET a = 3 WHERE b = 'def'; sqlite> SELECT * FROM t; 1|xyz 3|def sqlite> SELECT * FROM t1; 2|3 sqlite> Handy! Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
