On 23 Oct 2009, at 1:23am, D. Richard Hipp wrote: > On Oct 22, 2009, at 7:11 PM, Igor Tandetnik wrote: >> >> In SQLite, ROWID _is_ the unique identifier for each record. Any >> field declared INTEGER PRIMARY KEY is simply an alias for ROWID. >> "Position in the set", whatever it is, has nothing to do with it. > > A clarification: > > The ROWID is always unique, but it not guaranteed to be static. > ROWIDs can and often does change when you VACUUM. Except, if you > declare an INTEGER PRIMARY KEY, then the IPK will be the same as the > rowid and the IPK will not change because of VACUUM. Moral: Always > use INTEGER PRIMARY KEY, never a raw ROWID, for anything that you want > to persist for longer than the current transaction.
That helps. What you're saying is that if I ever intend to use ROWID I should explicitly declare a column as INTEGER PRIMARY KEY. I can live with that. So now my original query comes down to this: if a column's value is generated by SQLite rather than being supplied by my INSERT command, i.e. if the column has a is INTEGER PRIMARY KEY, or has a DEFAULT value, can I rely on being able to access that value in my TRIGGER as NEW.columnName ? I note that the documentation about TRIGGER does warn against certain tricks while referring to OLD.columnName and also against other tricks using BEFORE TRIGGERs so I won't be doing any of that. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
