title: SQLite Efficient Running Total
body: <p>I have a table of transactions in SQLite</p>
  
  <p>number    date   Category   Amount   runningBalance</p>
  
  <p>I want the running balance column to have a running sum of the amount column after the table is sorted by Date first and number second.</p>
  
  <p>I can do this with a select when reading. But this table has the potential to get very large and I don't want to recalculate every time. I want to make a trigger where all the transactions following (by date then number) the inserted/edited transaction have their runningBalance value updated. </p>
  
  <p>This will mean that the calculations are reduced... as more recent transactions are likely to be edited more often, and older ones rarely. It also will spread the computation over writes so that reads are near instant.</p>
  
  <p>Can anyone provide assistance on how to set up such a trigger?</p>
  
  <p>so far this is what I have but it does not give desired results. And recalculates all every time. Not just the ones following the change.</p>
  
  <pre><code>CREATE TRIGGER RunningTotal AFTER UPDATE ON Transactions FOR EACH ROW
  BEGIN
      UPDATE Transactions 
         SET RunningBalance = (
          SELECT (
                     SELECT sum(Amount) 
                       FROM TopInfo t2
                      WHERE t2.Date &lt;= t1.Date
                 )
            FROM Transactions t1
      );
  END;
  </code></pre>
  
  <p>Thanks!</p>