Having Googled the topic I realise that this has been raised on several previous occasions over many years, but I have a use case for accessing a temporary table from a trigger and can't find a way around it. I don't think my problem is exceptional, so if anyone can suggest an alternative strategy I'd be most grateful.

My underlying issue is as follows: I have data that is very long lived and can't rely on applications -- as yet unwritten -- to generate surrogate keys in a consistent way. I would prefer to accept as row ids any unique string that the application wishes to pass, internally create a new unique identifier (a uuid) and map one to the other. [Note, this is test code; if the approach can be made to work uuid be changed to include a logical clock so that databases from different devices can be more easily merged.]

    uuid TEXT DEFAULT( hex( randomblob(16))),
    id   TEXT UNIQUE NOT NULL

);

Applications access tables through views with INSTEAD OF INSERT etc. triggers so that I can hide other internal metadata such as when and by whom a row was inserted. A typical insert trigger is:

CREATE TRIGGER "i_Fact" INSTEAD OF INSERT ON "Fact"
BEGIN
    INSERT INTO k_map (id) VALUES (NEW.uuid);
    INSERT INTO "Fact_t" (
          uuid
-- more columns
    ) VALUES (
          (SELECT uuid FROM k_map WHERE id = NEW.uuid)
-- more values
    );
END;

For foreign keys the value is COALESCE( SELECT uuid FROM k_map WHERE id = [APP FK], NEW.[FK]). I have referential integrity enabled to make sure that which ever is used, it is a valid FK for the column in question.

Applications can choose to read k_map to maintain consistency between internal and persisted references, but for the duration of a connection they don't have to.

I very much need k_map to be unique to a connection, to be created when a connection is initiated and destroyed on the connection closing. A TEMPORARY table would suit the job, but it's not possible to use one in a trigger. Ideally, I'd like to prefix the trigger with CREATE TEMPORARY TABLE IF NOT EXISTS so that it is created automatically the very first time a row is inserted for any given connection. This too is not possible. Of even greater utility would be a mechanism to call a block of SQL whenever a connection or transaction is opened or closed (committed).

From my reading it seems that the SQLite code -- which I freely admit I haven't read -- actually goes to some length to prevent qualified table names (temp.k_map) being accessed from triggers. It seems to have been considered more dangerous than useful. May I suggest that can indeed be useful? Perhaps the present behaviour could be protected by a new PRAGMA?

In the mean time, can anybody suggest an alternative strategy I could try? I'm happy to install (robust) extensions if that's necessary.

Many thanks.

You need to create your view and instead of triggers in the temp database (when the connection is opened). Example:

create table t_data
(
    uuid uuid blob not null on conflict replace default (randomblob(16)),
    data
);

create table keymap
(
    uuid        uuid blob not null,
    key         text not null,
    primary key (uuid, key),
    unique (key, uuid)
) WITHOUT ROWID;

create temporary view data
as
    select keymap.key,
           data
      from t_data, keymap
     where t_data.uuid == keymap.uuid;

create temporary trigger i_data instead of insert on data
begin
    insert into t_data values (NULL, new.data);
    insert into keymap values ((select uuid from main.t_data where rowid=last_insert_rowid()), new.key);
end;

You can also put keymap in temp if you wish.

Triggers in any schema other than temp may only access objects in their own schema. However, triggers in temp may access any object by name, even cross-schema.

Many thanks for the prompt response and suggestion.

Is there any way to create the temporary view and trigger automatically when a new connection is opened? Something equivalent to an ON CONNECT trigger (which I know doesn't exist)? Or must the application create the temporary view and trigger immediately after the connection is opened? I've been trying to find a solution where the application doesn't have be involved in the set up, hence my initial thought that the first insert into the database for a given connection would create the temporary keymap table if it didn't already exist.

For background, the domain of interest is farming and, although there may not be a huge amount of data, some of it could be held for decades. Much data capture will be done on mobile devices, hence the choice of SQLite and a desire to use uuids internally to facilitate merging/ replicating datasets. The intention is to open source the database table schema, a data access schema, the SQL to create an empty database and various ontologies (breeds of animal, diseases, veterinary drugs and a lot else). It's not possible to predict what future applications may be written against the database, let alone what language they'll be written in, so it's not attractive to write a data access layer to wrap whatever SQLite access libraries might end up being used.

predict ... what language they'll be written in, so it's not attractive to write a data access layer

Unfortunately, auto-extension, as suggested by Keith, is still in the app above SQLite.

You're out of luck for something application-independent, directly in the persistent DB file itself.

Remember that SQLite is an embedded DB engine, and thus expects to be hosted by an application, which matters as much as the DB's SQL itself.