title: SQL trigger function to UPDATE daily moving average upon INSERT
body: <p>I am trying to create a SQL trigger function which should UPDATE a column when data is INSERTed INTO the table. The update is based on the values present in the values being INSERTed.</p>
  
  <p>I have the following table to store daily OHLC data of a stock.</p>
  
  <pre><code>CREATE TABLE daily_ohlc (
   cdate date,
   open numeric(8,2),
   high numeric(8,2),
   low numeric(8,2),
   close numeric(8,2),
   sma8 numeric(8,2)
  );
  </code></pre>
  
  <p>INSERT command:</p>
  
  <pre><code>INSERT INTO daily_ohlc (cdate, open, high, low, close) 
   values ('SYMBOL', 101, 110, 95, 108);
  </code></pre>
  
  <p>When this command is executed I would like to update the 'sma8' column based on the present values being INSERTed and the values already available in the table.</p>
  
  <p>As of now, I am using the following SQL query to calculate the values for every row and then use the result to update the 'sma8' column using python.</p>
  
  <pre><code>SELECT sec.date, AVG(sec.close) 
   OVER(ORDER BY sec.date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS 
  simple_mov_avg FROM daily_ohlc sec;
  </code></pre>
  
  <p>The above query calculates Simple Moving Average over the last 8 records (including the present row).</p>
  
  <p>Using this procedure I update every row of data in the 'sma8' column every time I insert data. I would like to update only the last row (i.e row being INSERTed) by using a trigger. How to do this?</p>