On 11/15/08, Craig Smith <[EMAIL PROTECTED]> wrote: > I am addicted to using INTEGER PRIMARY KEY AUTOINCREMENT as the id > fields in my SQLite projects, yet I would like to try some triggers as > well. Of course, every time I add a trigger that accesses a table > with these types of id fields, all sorts of odd things happen.
Do you mind specifying what are the "odd things" that happen? I have used TRIGGERs on tables with INTEGER PRIMARY KEY and have had no odd things happen at all. > I > read in the documentation about simply using PRIMARY KEY instead of > INTEGER PRIMARY KEY, but then I lose my AUTOINCREMENT ability. > > Does anyone have a solution that both provides AUTOINCREMENT (or a > reasonable facsimile) to a PRIMARY KEY field and allows the usage of > triggers on those tables? INTEGER PRIMARY KEY provides the autoincrement facily... it is just an alias for the internal ROWID which can also be accessed via the keywords ROWID, _ROWID_ or OID. On the other hand, if you add the keyword AUTOINCREMENT to INTEGER PRIMARY KEY, a subtle different takes place -- with just INTEGER PRIMARY KEY, if a row is deleted, its PRIMARY KEY can be reused. On the other hand, with the AUTOINCREMENT keyword, every new PRIMARY KEY is Max(PRIMARY KEY) + 1. Other than that, there is no interference with TRIGGER behaviors. If you have experienced any, why don't you detail them here. > > Thank you very much. > > Craig Smith > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users