MySQL Trigger Value from Previous Row with Same Column Name

Question

Inventory Table:

Inventory History Table:

The query:

INSERT INTO inventory_history (SKU, Quantity, timestamp)
SELECT SKU, Quantity, modifiedtime FROM inventory WHERE modifiedtime BETWEEN '2016-12-25 00:00:00' AND '2016-12-26 00:00:00';

The Trigger:

CREATE TRIGGER `sold_diff` BEFORE INSERT ON `inventory_history`
FOR EACH ROW begin
declare prev_quantity int(11) default 0;
declare prev_sku varchar(255) default null;

select sku 
into prev_sku
from inventory_history
where prev_sku = NEW.sku
order by id desc
limit 1;

select quantity
into prev_quantity 
from inventory_history
order by id desc
limit 1;

set NEW.sold = prev_quantity
;
end

The Result:

Now, how it's set-up is it's taking prev_quantity from the previous row, and putting it into the sold column.

I can not figure out a way to bind SKU in with prev_quantity, so that it will give me the previous Quantity value from the corresponding SKU.

Desired Result:

I've messed with a variety of different WHERE clauses on the two declared, but nothing is working right.. so I'm thinking this is not the right path to take.

How can this be achieved?

Answer 1

I think you are taking the wrong approach.

You seem to want an insert on the inventory table. When a new value is inserted or updated (or deleted), you then insert a row in the inventory_history table with the old and new values.

You then don't need an explicit insert on inventory_history.