Select value from both OLD TABLE and FINAL TABLE during UPDATE

Question

I have an UPDATE statement I've written that will take a @CHANGE (positive or negative integer value) and update the CURRENT_INVENTORY for that type of item.

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;

I'd like to know what CURRENT_INVENTORY is before and after I run this update. Is there a way to select both the OLD TABLE and FINAL TABLE value of CURRENT_INVENTORY?

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.

I don't think I can select both OLD and FINAL so I believe the best solutions I have are either:

1. Select value from OLD TABLE and duplicate the logic from the CASE statement to derive the new value
2. Select value from OLD TABLE and select the new value (within the same transaction now that my UPDATE has locked the row)

Is there a better/correct way to accomplish this?

Answer 1

You can use "select from update" for that (SQL manual). Example:

SELECT EMPNO, SALARY, BONUS , OLD_SALARY , OLD_BONUS
    FROM FINAL TABLE
        (UPDATE EMP  INCLUDE  ( OLD_SALARY DECIMAL(9,2)
                              , OLD_BONUS DECIMAL(9,2))
        SET SALARY     = SALARY+1000
        ,   BONUS      = 0
        ,   OLD_SALARY = SALARY
        ,   OLD_BONUS  = BONUS
        WHERE SALARY > 28000);

Answer 2

Updating inline view instead of table:

SELECT CURRENT_INVENTORY, CURRENT_INVENTORY_OLD
FROM FINAL TABLE(
  UPDATE (SELECT *, CURRENT_INVENTORY AS CURRENT_INVENTORY_OLD 
          FROM ITEM_INVENTORY)
  SET CURRENT_INVENTORY = CASE 
                             WHEN CURRENT_INVENTORY + @CHANGE < 0 THEN 0
                             ELSE CURRENT_INVENTORY + @CHANGE
                           END,
       LAST_UPD_TSTAMP = CURRENT_TIMESTAMP,
       LAST_UPD_SOURCE = @SOURCE
  WHERE LOCATION = @LOCATION
    AND ITEM_TYPE = @ITEM_TYPE
);