>> It is easily doable by having a AFTER INSERT OR UPDATE trigger or add a >> field with CURRENT_TIMESTAMP as default. Though, of course, >> this will only help you get the last COMMITTED change of the table. > >Yes and it won't work for deletes.
Well, a BEFORE DELETE trigger would. >There is also a gotcha I have seen in the field, where people maintain a >single table with one row per table and some >sort of last changed field. Or even worse a SINGLE record with a last updated >timestamp field per table. > >This results in a pure hotspot table (bottleneck) from a >concurrency/throughput POV. Sure it is possible to implement this in a way that effectively makes your system serialized rather than concurrent, but that is avoidable by doing things similar to what can be done for keeping counts in a table, e.g. have a table LAST_UPDATED (ID INTEGER, TABLENAME VARCHAR(31), LAST_UPDATED TIMESTAMP), that triggers insert into (never update) and that you regularly issue DELETE FROM LAST_UPDATED L1 WHERE EXISTS(SELECT * FROM LAST_UPDATED L2 WHERE L1.TABLENAME = L2.TABLENAME AND L1.LAST_UPDATED < L2.LAST_UPDATED). Then running SELECT TABLENAME, MAX(LAST_UPDATED) FROM LAST_UPDATED GROUP BY 1 would get you information about when a particular table was last updated (excepting uncommitted changes). That would avoid the concurrency issue your mentioning, but of course, it still affects the throughput and is not a viable option for heavily loaded systems. >This all would be a very nice use case for per-statement trigger instead of >per-row trigger, which Firebird doesn't support, unfortunately. I agree that per statement triggers could be a lot better in situations like this, Thomas (provided DML is done in batches as opposed to having lots of simultaneous users). Set