Igor, yes, deletes do occur on <table_name>, but before every insert on <table_name> I delete all records from mods_log_<table_name> and nevertheless get duplicates. I would expect the implicit index on modrowid to be refreshed too, when mods_log_<table_name> gets emptied.
Thank you, Alex ------------------------------------- Date: Tue, 14 Apr 2009 00:08:38 -0400 From: "Igor Tandetnik" <itandet...@mvps.org> Subject: Re: [sqlite] Implicit indices on TEMP tables To: sqlite-users@sqlite.org Message-ID: <gs1287$77...@ger.gmane.org> "Alex Ousherovitch" <alex.ousherovi...@opentv.com> wrote in message news:5ee1928d06817b4788b64caf1a8517b00332a...@sfo-ex-01.ad.opentv.local > I am creating a set of the following TEMP tables > > CREATE TEMP TABLE IF NOT EXISTS mods_log_<table_name> (modrowid > INTEGER NOT NULL PRIMARY KEY) > > for every connection on my database, using the same name. These TEMP > tables AND their indices are supposed to be only visible within that > same database connection. > > But it appears that when populated from TEMP triggers, also created > for each connection - > > CREATE TEMP TRIGGER IF NOT EXISTS mods_log_<table_name>_trigger > AFTER INSERT ON <table_name> > FOR EACH ROW BEGIN > INSERT INTO mods_log_<table_name> VALUES (new.rowid); > END > > -I get SQLITE_CONSTRAINT error for attempting to add non-unique > elements. Do you ever delete from <table_name>? When you do so, do you also delete a matching record from mods_log_<table_name>? It's possible that a ROWID from a deleted record is reused for new record in <table_name>, which would be a duplicate in mods_log_<table_name>. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
