title: Select value from both OLD TABLE and FINAL TABLE during UPDATE
body: <p>I have an UPDATE statement I've written that will take a <code>@CHANGE</code> (positive or negative integer value) and update the <code>CURRENT_INVENTORY</code> for that type of item.</p>
  
  <pre><code>UPDATE ITEM_INVENTORY
  SET    CURRENT_INVENTORY = MAX(CURRENT_INVENTORY + @CHANGE, 0),
         LAST_UPD_TSTAMP = CURRENT_TIMESTAMP,
         LAST_UPD_SOURCE = @SOURCE
  WHERE  LOCATION = @LOCATION
         AND ITEM_TYPE = @ITEM_TYPE;
  </code></pre>
  
  <p>I'd like to know what <code>CURRENT_INVENTORY</code> is before and after I run this update. Is there a way to select both the <code>OLD TABLE</code> <em>and</em> <code>FINAL TABLE</code> value of <code>CURRENT_INVENTORY</code>?</p>
  
  <p>I'm wanting to display a message along the lines of "{location} inventory changed from {old} to {new}". I could use a separate SELECT to get the old/current value, do the math in my code, and then UPDATE the row on the table. I believe that'd be susceptible to race conditions though (since the first SELECT won't lock the row) so I'm attempting to do it in a single query.</p>
  
  <p>I don't think I can select both OLD and FINAL so I believe the best solutions I have are either:</p>
  
  <ol>
  <li>Select value from <code>OLD TABLE</code> and duplicate the logic from the CASE statement to derive the new value</li>
  <li>Select value from <code>OLD TABLE</code> and select the new value (within the same transaction now that my UPDATE has locked the row)</li>
  </ol>
  
  <p>Is there a better/correct way to accomplish this?</p>