MySQL Forums
Forum List  »  Triggers

What is wrong with this
Posted by: Andr� Carvalho
Date: June 21, 2010 02:13PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
What is wrong with this
1840
June 21, 2010 02:13PM
1101
June 22, 2010 08:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.