0

Ok so I'm putting together a finance database in Sequel Pro (the MySQL app for Mac). I've got a table that logs prices/stock-levels and I'll have a 2nd table that maintains calculated values like deltas (day-to-day changes) and eventually moving averages etc. I figure it's better to calculate these things once when values are updated/inserted rather than when pulling data out.

I'm having to use temp tables with row_number() equivalents to deal with calculating business days rather than just regular days or non-sequential Dates/IDs

Essentially the problem is that the (After Update) Trigger doesn't seem to do anything, though it also doesn't seem to flag any fail either. I just go to test it by changing some existing values and the calculated fields remain the same. I'm guessing this is a syntax thing? SequelPro/MySQL is a bit weird for me still seeing as I'd normally have SQL Server at my day job...

BEGIN

CREATE TEMPORARY TABLE IF NOT EXISTS predata AS
(SELECT thedate
,price_cash
,price_3m
,stocks
FROM aluminium
WHERE thedate BETWEEN DATE_ADD(NEW.thedate,INTERVAL -6 DAY)
AND DATE_ADD(NEW.thedate,INTERVAL 5 DAY));

CREATE TEMPORARY TABLE IF NOT EXISTS temp1 AS
(SELECT t.*, @rownum := @rownum + 1 AS rank
FROM predata t,
(SELECT @rownum := 0) r
ORDER BY thedate ASC);

CREATE TEMPORARY TABLE IF NOT EXISTS temp2 AS(SELECT * FROM temp1);

CREATE TEMPORARY TABLE IF NOT EXISTS result_delta AS
(
SELECT b.thedate
,(b.price_cash - a.price_cash) AS delta_cash
,(b.price_3m - a.price_3m) AS delta_3m
,(b.stocks - a.stocks) AS delta_stocks
FROM temp1 a
INNER JOIN temp2 b ON b.rank-1 = a.rank
WHERE b.thedate = NEW.thedate
OR a.thedate = NEW.thedate
);

UPDATE aluminium_calcs a
INNER JOIN result_delta r ON a.thedate = r.thedate
SET a.delta_cash = r.delta_cash
, a.delta_3m = r.delta_3m
, a.delta_stocks = r.delta_stocks;

END
CalvinGraham
  • 11
  • 3
  • Is this the code for the trigger? Why aren't you using `old` and `new` to reference the line being edited? – Fredster Aug 01 '17 at 14:01
  • Yes. Well I'm using NEW.thedate to pick out the row (day) being updated. Then that entry is used with the day before/after to calculate the day to day changes and update the 'calculations' table at the end. The trigger is on the table that contains just the basic price/stock data. The "_calcs" table logs the calculated values – CalvinGraham Aug 01 '17 at 15:17
  • I'd started to think that perhaps this was too much stuff to be sticking in a trigger, perhaps it would be better to bundle it all up in a set of stored procedures for the various different calculated columns/metrics and use the trigger to call the procedures with the date as a parameter? But then question 10657 says to definitely not do that. – CalvinGraham Aug 01 '17 at 16:10

1 Answers1

0

In the end I just decided to build a set of extra code in php and add Include('deltas_update.php') to my update screen. It's probably a better solution anyway as it's easier to do the calculations with proper arrays/loops and pho code than SQL. MySQL is quite bare bones but it's probably better that it pushes the heavy work elsewhere where it's more easily visible/traceable

CalvinGraham
  • 11
  • 3