title: How to read all "last_changed" records from Firebird DB?
body: <p>My question is a bit tricky, because it's mostly a logical problem.<br>
  I've tried to optimize my app speed by reading everything into memory but only those records, which changed since <em>"last read"</em> = greatest timestamp of records last time loaded.  </p>
  
  <p>FirebirdSQL database engine does not allow to update a field in an "After Trigger" directly, so it's obviously using "before update or insert" triggers to update the field <code>new.last_changed = current_timestamp;</code>  </p>
  
  <h2>The problem:</h2>
  
  <p>As it turns out, this is a totally WRONG method, because those triggers fire on transaction <strong>start</strong>!<br>
  So if there is a transaction that takes some more time than an other, the saved "last changed time" will be lower than a short-burst transaction fired and finished in between.<br>
  <code>1. tr.:  13:00:01.400 .............................Commit</code>  &lt;&lt; this record will be skipped !<br>
  <code>2. tr.:           13:00.01.500......Commit</code> &lt;&lt; reading of data will happen here.<br>
  The next read will be <code>&gt;= 13:00.01.500</code>  </p>
  
  <h2>I've tried:</h2>
  
  <p>to rewrite all triggers, so they fire <strong>after</strong> and call an <code>UPDATE orders SET ...</code> &lt;&lt; <em>but this causing circular, self-calling endless events.</em><br>
  Would a <code>SET_CONTEXT</code> <a href="https://stackoverflow.com/a/9290926">lock</a> interfere with <strong>multi-row</strong> update and <strong>nested</strong> triggers?<br>
   <em>(I do not see any possibility this method would work <strong>good</strong> if running multiple updates in the same transaction.)</em>  </p>
  
  <p>What is the common solution for all this?</p>
  
  <h2>Edit1:</h2>
  
  <p>What I want to happen is to read <strong>only</strong> those records from DB actually changed since last read. For that to happen, I need the engine to update records AFTER COMMIT. (Not during it, "in the middle".)<br>
  This trigger is NOT good, because it will fire on the moment of change, (not after Commit):  </p>
  
  <pre><code>alter trigger SYNC_ORDERS active after insert or update position 999 AS
  declare variable N timestamp; 
  begin
    N = cast('NOW' as timestamp);
    if (new.last_changed &lt;&gt; :N) then
      update ORDERS set last_changed= :N where ID=new.ID;
  end
  </code></pre>
  
  <p>And from the application I do:  </p>
  
  <pre><code>Query1.SQL.Text := 'SELECT * FROM orders WHERE last_changed &gt;= ' + DateTimeToStr( latest_record );  
  Query1.Open;  
   latest_record := Query1.FieldByName('last_changed').asDateTime;   
  </code></pre>
  
  <p><em>.. this code will list only the record commited in the 2th transaction (earlier) and never the first, longer running transaction (commited later).</em></p>
  
  <h2>Edit2:</h2>
  
  <p>It seems I have the same question as <a href="https://stackoverflow.com/questions/3809751/determining-row-changes-since-last-access-in-sql-server">here...</a> , but specially for FirebirdSQL.<br>
  There are not really any good solutions there, but gave me an idea:<br>
  - What if I create an extra table and log changes earlier than 5 minutes there per table?<br>
  - Before each SQL query, first I will ask for any changes in that table, sequenced via ID grow!<br>
  - Delete lines older than 23 hours  </p>
  
  <pre><code>ID  TableID  Changed
  ===========================
  1   5   2019.11.27 19:36:21
  2   5   2019.11.27 19:31:19
  </code></pre>
  
  <h2>Edit3:</h2>
  
  <p>As Arioch already suggested, one solution is to:</p>
  
  <ul>
  <li>create a "logger table" filled on every <code>BEFORE INSERT OR UPDATE</code>
  trigger by every table </li>
  <li>and update the "last_changed" sequence of it
  by the <code>ON TRANSACTION COMMIT</code> trigger</li>
  </ul>
  
  <p>But, would not be ...</p>
  
  <h2>a better approach?:</h2>
  
  <ul>
  <li>adding 1-1 <code>last_sequence INT64 DEFAULT NULL</code> column to every table</li>
  <li>create a global generator <code>LAST_GEN</code></li>
  <li>update every table's every NULL row with a gen_id(LAST_GEN,1) inside the <code>ON TRANSACTION COMMIT</code> trigger</li>
  <li>SET to NULL again on every <code>BEFORE INSERT OR UPDATE</code> trigger</li>
  </ul>
  
  <p>So basically switching the <code>last_sequence</code> column of a record to:<br>
   <code>NULL &gt; 1 &gt; NULL &gt; 34</code> ... every time it gets modified.<br>
  This way I :</p>
  
  <ul>
  <li>do not have to fill the DB with log data, </li>
  <li>and I can query the tables directly with <code>WHERE last_sequence&gt;1;</code>. </li>
  <li>No needed to pre-query the "logger table" first.</li>
  </ul>
  
  <p>I'm just afraid: WHAT happens, if the <code>ON TRANSACTION COMMIT</code> trigger is trying to update a <code>last_sequence</code> field, <strong>while</strong> a 2th transaction's ON BEFORE trigger is locking the record (of an other table)?<br>
  Can this happen at all?  </p>