title: Automatically update a value when inserting/removing another
body: <p>We have a table like the one below:</p>
  
  <pre><code>=# CREATE TABLE items (item_id INT NOT NULL, revision VARCHAR(2) NOT NULL, is_newest_rev BOOLEAN NOT NULL, UNIQUE (item_id,revision));
  
  ...
  
  =# SELECT * FROM items;
   item_id | revision | is_newest_rev
  ---------+----------+---------------
       250 |       1A | f
       250 |       1B | t
       199 |       1F | t
        40 |       1A | f     &lt;-note this, false
        40 |       1B | f     &lt;-note this, false
        40 |       1C | t     &lt;-note this, true
  </code></pre>
  
  <p>The above table is valid.</p>
  
  <p>The following table is invalid:</p>
  
  <pre><code>=# SELECT * FROM items;
   item_id | revision | is_newest_rev
  ---------+----------+---------------
       250 |       1A | f
       250 |       1B | t
       199 |       1F | t
        40 |       1A | f     &lt;-note this, false
        40 |       1B | f     &lt;-note this, false
        40 |       1C | t     &lt;-note this, true
        40 |       1D | t     &lt;-ERROR! 40 | 1C is already marked as true!
  </code></pre>
  
  <p>The following table is <em>also</em> invalid:</p>
  
  <pre><code>=# SELECT * FROM items;
   item_id | revision | is_newest_rev
  ---------+----------+---------------
       250 |       1A | f
       250 |       1B | t
       199 |       1F | t
        40 |       1A | f
        40 |       1B | f
        40 |       1C | f &lt;-ERROR! 40 has no true values!
  </code></pre>
  
  <p>I need to create a trigger (I think that's what it is) to automatically change the only true value in the list for that revision and item id to being false, and then add the new value specified in an insert query.</p>
  
  <p>In other words I need it to match the following:</p>
  
  <ul>
  <li><p>Reject any insert queries attempting to insert <code>f</code> into the <code>is_newest_rev</code> column (only accept <code>t</code> insert queries)</p></li>
  <li><p>Automatically change the only instance of <code>t</code> in the table (for that item_id and revision) to <code>f</code> before adding the new value</p></li>
  <li><p>Deleting one or more <code>t</code> rows will automatically change the other <code>is_newest_rev</code> values (for that item_id and revision) to <code>t</code></p></li>
  <li><p>Always make sure there is at least one instance of <code>t</code> for that item_id and revision (never 0, never 2, exactly 1)</p></li>
  </ul>
  
  <p>Example:</p>
  
  <pre><code>=# SELECT * FROM items;
   item_id | revision | is_newest_rev
  ---------+----------+---------------
       250 |       1A | f
       250 |       1B | t
       199 |       1F | t
        40 |       1A | f
        40 |       1B | f
        40 |       1C | t
  
  =# INSERT INTO items VALUES (40, '1D', 't');
  INSERT 0 1
  
  =# SELECT * FROM items;
   item_id | revision | is_newest_rev
  ---------+----------+---------------
       250 |       1A | f
       250 |       1B | t
       199 |       1F | t
        40 |       1A | f
        40 |       1B | f
        40 |       1C | f     &lt;-
        40 |       1D | t     &lt;- notice how 1C is now false and 1D is now true
  
  =# DELETE FROM items WHERE item_id = '40' AND revision = '1A';
  DELETE 1
  
  =# SELECT * FROM items;
   item_id | revision | is_newest_rev
  ---------+----------+---------------
       250 |       1A | f
       250 |       1B | t
       199 |       1F | t
        40 |       1B | f
        40 |       1C | f
        40 |       1D | t      &lt;- note all other values stayed the same
  
  =# DELETE FROM items WHERE item_id = '40' AND is_newest_rev = 't';
  DELETE 1
  
  =# SELECT * FROM items;
   item_id | revision | is_newest_rev
  ---------+----------+---------------
       250 |       1A | f
       250 |       1B | t
       199 |       1F | t
        40 |       1B | f
        40 |       1C | t     &lt;- note how 1D was removed and 1C was marked as true
  
  =# DELETE FROM items WHERE is_newest_rev = 't';
  DELETE 3
  
  =# SELECT * FROM items;
   item_id | revision | is_newest_rev
  ---------+----------+---------------
       250 |       1A | t     &lt;- 250, 1B is removed, 1A marked as true
        40 |       1B | t     &lt;- 199, 1F completely removed, as no other 199s exist
                              &lt;- 40, 1C is removed, 1B marked as true
  </code></pre>
  
  <p>I hope this explains enough what I'm looking for. If you need me to explain more let me know</p>