temp storage within triggers
It is not possible to create or use temp tables or cte's inside (instead of) triggers. Yet I need a storage for intermediate results, generated within the trigger. So I created a normal table to be used as a 'tempstore' for these kind of data. At the end of the trigger the generated data are deleted from the table, leaving it empty for the next use.
For as I can see now there will be only one connection that writes. Is this sound/safe, is there an other/better way?
It should be safe, even with multiple connections, because a trigger is always used in the context of an implicit or explicit transaction.
In all journal modes, there is always only one writer. Triggers launch for insert, update, or delete, which are all writers.
So, the normal table you use as 'tempstore' will only be accessed by one connection at any time.
-- Regards, Kees Nuyt