Access previous row value in MySQL trigger

Question

I have four columns in table1 in MySQL (Date, Open, High, Low, Close, Calculation). I am trying to figure out a way to create a trigger such that when ever a new value is updated in table 1, the Calculation field gets updated using previous rows value. I am currently using the below trigger to update Calculation field using current High, current Low and current close

DELIMITER //
create TRIGGER `updatetable1` before insert ON `table1` FOR EACH ROW begin
    set NEW.Calculation=((NEW.High-New.Low)*0.118)+NEW.Close;
   end //
DELIMITER ;

Now I would like to modify the formula a little bit using the following logic :

Calculation (i) =((High(i-1) -Low(i-1))*0.118)+Close(i-1);

How could I access the previous row value's of High, Low and Close in this trigger to update current row value of Calculation ?

Example:

Answer 1

You could rewrite your trigger as follows:

DELIMITER //

create TRIGGER `updatetable1` before insert ON `table1` FOR EACH ROW 
begin
    declare prev_low float;
    declare prev_high float;
    declare prev_close float;

    select   low, high, close
    into     prev_low, prev_high, prev_close 
    from     table1
    order by date desc
    limit 1;

    set NEW.Calculation=(prev_high-prev_low)*0.118 + prev_close;
end//

DELIMITER ;

SQL fiddle

Change the datatype of prev_low (and other variables) to whichever datatype you use in your table.