title: Before and After trigger on the same event? Fill a child table PostgreSQL
body: <p><strong>Situation</strong></p>
  
  <p>I have a database in PostgreSQL 9.5 used to store object locations by time.</p>
  
  <p>I have a main table named "position" with the columns (only relevant):</p>
  
  <ul>
  <li><code>position_id</code></li>
  <li><code>position_timestamp</code></li>
  <li><code>object_id</code></li>
  </ul>
  
  <p>It is partitioned into 100 child tables on object_id with the condition:</p>
  
  <pre><code>CREATE TABLE position_object_id_00
  ( CHECK object_id%100 = 0 ) 
  INHERITS ( position );
  </code></pre>
  
  <p>And so on for the others children. I partitioned with a modulus relation to distribute equally the objects.
  Each child is indexed on <code>position_id and object_id</code> (two different indexes).</p>
  
  <p>The trigger to redirect inserts on children is:</p>
  
  <pre><code> CREATE TRIGGER insert_position_trigger
       BEFORE INSERT ON position
       FOR EACH ROW EXECUTE PROCEDURE insert_position();
  </code></pre>
  
  <p>And the procedure <code>insert_position()</code> looks for the right child table to insert the data, inserts it and then return the NEW object:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION insert_position() RETURNS TRIGGER AS $insert_position$
    DECLARE
  
    BEGIN
      --Look for child table
      [...]
      --Insert data in right child table
      [...]
  
      RETURN NEW;
    END;
  $insert_position$ LANGUAGE plpgsql;
  </code></pre>
  
  <p>I have a summary table <code>object_last_known_position</code> with the same columns that is updated with the trigger:</p>
  
  <pre><code>CREATE TRIGGER update_object_last_known_position 
     AFTER INSERT OR UPDATE ON position 
     FOR EACH ROW
     EXECUTE PROCEDURE update_object_last_known_position();
  </code></pre>
  
  <p>The procedure <code>update_object_last_known_position()</code> basically checks if the <code>position_timestamp</code> is more recent, then delete the older entry and create a new entry with the data passed on the INSERT or UPDATE query (NEW).</p>
  
  <p><strong>Issue</strong></p>
  
  <p>So these two triggers react to the same event: insert on position, one is before, the other is after
  Returning new for <code>insert_position()</code> allows me to use NEW in the trigger <code>update_object_last_known_position()</code>, and this is absolutely necessary. But doing that, it also insert the data on the master table position. So my data is now duplicated.</p>
  
  <p>I tried to put the two triggers before, they both execute when I insert data if I let it like that, but if I remove the "return new" from the procedure <code>insert_position()</code>, <code>update_object_last_known_position()</code> is not executed.</p>
  
  <p>I am stuck with this issue and I didn't find a way to execute both of these triggers without filling the master table position when I insert data. </p>
  
  <p>So if you have any ideas, I will really appreciate :)</p>
  
  <p>Thank you for your help!</p>
  
  <p><strong>EDIT</strong></p>
  
  <p><strong>Solution</strong></p>
  
  <p>Thanks to the answer</p>
  
  <p>I "merged" my two triggers: <code>insert_position()</code> now calls <code>update_object_last_known_position</code> directly. For that, I modified <code>update_object_last_known_position</code> to a stored procedure with a parameter. The parameter is the id of the position <code>insert_position()</code> just created, so I am able to find it and retrieve information.
  (Calling <code>update_object_last_known_position</code> inside the other trigger means we cannot use <code>NEW</code> anymore)
  And obviously return type for <code>insert_position()</code> is now <code>NULL</code>, and everything works fine :)</p>