Description:
i m using mysql version 5.0 and i have problem in creating update trigger.......
CREATE TABLE RT_PORTFOLIO (
portfolioId varchar(32) NOT NULL,
portfolioName varchar(40) NOT NULL,
userId varchar(32) NOT NULL REFERENCES CORE_USER(userId),
currentValuation FLOAT NOT NULL default '0.0',
investmentAmount FLOAT NOT NULL default '0.0',
PRIMARY KEY (portfolioId)
);
/*Table structure for table RT_PORTFOLIO */
DROP TABLE IF EXISTS RT_PORTFOLIO_DETAILS;
CREATE TABLE RT_PORTFOLIO_DETAILS (
portfolioDetailsId varchar(32) NOT NULL,
portfolioId varchar(40) NOT NULL,
symbol varchar(40) NOT NULL,
currentPrice FLOAT NOT NULL default '0.0',
buyPrice FLOAT NOT NULL default '0.0',
PRIMARY KEY (portfolioDetailsId)
}
and trigger that i have created is as below:
DELIMITER $$
DROP TRIGGER `realtrade`.`RT_PORTFOLIO_TU`$$
create trigger RT_PORTFOLIO_TU After update on rt_portfolio_details FOR EACH ROW BEGIN
BEGIN
Declare var1 float;
Declare var2 float;
select var1 = SUM (NEW.currentPrice * RT_PORTFOLIO_DETAILS.quantity) from RT_PORTFOLIO_DETAILS where RT_PORTFOLIO_DETAILS.portfolioId = NEW.portfolioId
and RT_PORTFOLIO.portfolioId = NEW.portfolioId;
UPDATE RT_PORTFOLIO SET RT_PORTFOLIO.currentValuation = var1 where RT_PORTFOLIO.portfolioId = NEW.portfolioId;
select var2 = sum(RT_PORTFOLIO_DETAILS.buyPrice * RT_PORTFOLIO.quantity) from RT_PORTFOLIO_DETAILS where RT_PORTFOLIO_DETAILS.portfolioId = NEW.portfolioId;
update RT_PORTFOLIO set RT_PORTFOLIO.investmentAmount = var2 where RT_PORTFOLIO.portfolioId = NEW.portfolioId;
END$$
DELIMITER ;
but it is throwing error that result set cannot be returned in current version of mysql
please help out
How to repeat:
i m not able to get the result set using select statement in a variable
