On Fri, Jan 14, 2011 at 1:20 PM, Philip Graham Willoughby < phil.willoug...@strawberrycat.com> wrote:
> 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; > > Phil, it's an interesting solution. I will probably use it, but the current problem is that I have to resolve object context from non-oop function. It's possible (maybe even by hard-coding pointer as one of parameters in the trigger statement), but your variant is very elegant so I should think also of something similarly elegant :) You also mentioned at least one sane way of using select inside the trigger body. I'm not so familiar with triggers, just wondering is there some other examples of useful selects inside triggers bodies Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
