temp storage within triggers
(1) By ingo on 2020-06-19 06:23:29 [source]
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?
(2) By Kees Nuyt (knu) on 2020-06-19 19:27:12 in reply to 1 [link] [source]
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
(3) By ingo on 2020-06-21 12:03:36 in reply to 2 [link] [source]
Dank je Kees for confirmation. couldn't find an example using a 'tempstore' this way, or didn't use the right lingo when searching.