What is wrong with this
Hi there,
I have this TRIGGER, and I cant get the error on it:
-- Trigger DDL Statements
DELIMITER $$
USE `genyx`$$
CREATE TRIGGER tg_macd BEFORE INSERT ON cotacao
FOR EACH ROW BEGIN
DECLARE M9, M12, M26, RS14, MIN14, MAX14, RSC, RSA, RSB DECIMAL(13,2);
SET @codigo = NEW.codigo;
-- INICIO DO MACD
SET M9 = (SELECT SUM(fechamento) FROM (SELECT fechamento FROM cotacao WHERE codigo = @codigo ORDER BY DATA DESC LIMIT 8) as a);
SET M12 = (SELECT SUM(fechamento) FROM (SELECT fechamento FROM cotacao WHERE codigo = @codigo ORDER BY DATA DESC LIMIT 11) as b);
SET M26 = (SELECT SUM(fechamento) FROM (SELECT fechamento FROM cotacao WHERE codigo = @codigo ORDER BY DATA DESC LIMIT 25) as c);
SET NEW.MME9 = ROUND((M9 + NEW.fechamento)/9, 2);
SET NEW.MME12 = ROUND((M12 + NEW.fechamento)/12, 2);
SET NEW.MME26 = ROUND((M26 + NEW.fechamento)/26, 2);
-- FIM DO MACD E INICIO DO ESTOCASTICO
SET MIN14 = (SELECT MIN(minimo) from (SELECT minimo FROM cotacao WHERE codigo = @codigo ORDER BY DATA DESC LIMIT 13) as e);
SET MAX14 = (SELECT MAX(maximo) from (SELECT maximo FROM cotacao WHERE codigo = @codigo ORDER BY DATA DESC LIMIT 13) as f);
IF (MIN14 > NEW.fechamento) THEN
SET MIN14 = NEW.fechamento;
END IF;
IF (MAX14 < NEW.fechamento) THEN
SET MAX14 = NEW.fechamento;
END IF;
SET NEW.K = ROUND(((NEW.fechamento - MIN14) / (MAX14 - MIN14)) * 100, 2);
SET NEW.D = (SELECT SUM(K) from (SELECT K FROM cotacao WHERE codigo = @codigo ORDER BY DATA DESC LIMIT 2)as a);
SET NEW.D = ROUND((NEW.D + NEW.K)/3, 2);
-- FIM DO ESTOCASTICO E INICIO DO IFR
SET RSC = (SELECT COUNT(fechamento) FROM cotacao where codigo = @codigo AND fechamento > NEW.fechamento ORDER BY data DESC LIMIT 14) ;
SET RSA = (SELECT SUM(fechamento - NEW.fechamento) FROM (SELECT fechamento FROM cotacao where codigo = @codigo AND fechamento > NEW.fechamento ORDER BY data DESC LIMIT 13) as d);
SET RSA = RSA / RSC;
SET RSC = (SELECT COUNT(fechamento) FROM cotacao where codigo = @codigo AND fechamento < NEW.fechamento ORDER BY data DESC LIMIT 14) ;
SET RSB = (SELECT SUM(NEW.fechamento - fechamento) FROM (SELECT fechamento FROM cotacao where codigo = @codigo AND fechamento < NEW.fechamento ORDER BY data DESC LIMIT 13) as g);
SET RSB = RSB / RSC;
SET NEW.IFR = ROUND(100*(RSA/(RSA+RSB)), 2);
-- FIM DO IFR
END;
$$
Well, what happen is that the values of MME9, MME12 and MME26 are just fine, but the K is all messed up or just no value, where it shuld be one!
I tryed to debug doing this:
NEW.volume = MIN14;
volume is a field, and just show the value it had at the query, and not the new one.
I have all data from reading a long .txt file.
thx.