MySQL Forums
Forum List  »  Triggers

fetch result set using triggers
Posted by: cholleti varaprasad
Date: June 16, 2016 06:27AM

DELIMITER $$
CREATE TRIGGER `before_time_update`
BEFORE UPDATE ON `oprs_service_halts`
FOR EACH ROW
BEGIN

DECLARE cur_id INT;
DECLARE net_pr FLOAT(10,2);
DECLARE cur_r INT;
DECLARE remaind INT DEFAULT 0;
DECLARE avg_price FLOAT(10,2) DEFAULT 0;
DECLARE curs CURSOR FOR SELECT oprs_service_halt_bordings.boarding_time,
oprs_service_halt_bordings.boarding_period,
oprs_service_halt_bordings.boarding_day_count ,
oprs_service_halts.station_id
FROM test.oprs_service_halts
LEFT JOIN test.oprs_service_halt_bordings
ON test.oprs_service_halts.service_halt_id = test.oprs_service_halt_bordings.service_halt_id
AND test.oprs_service_halt_bordings.boarding_is_prime='Yes'
WHERE test.oprs_service_halts.service_id = '67'
ORDER BY test.oprs_service_halts.station_id ASC;

OPEN curs;
SET remaind=am;
read_loop:LOOP
FETCH curs INTO cur_id,net_pr,cur_r;
IF done THEN
LEAVE read_loop;
END IF;
IF (cur_r>=remaind) THEN
SET avg_price = avg_price + (net_pr * remaind);
UPDATE `people` SET `remains`=`remains`-remaind WHERE remains=cur_id;
LEAVE read_loop;
ELSE
SET avg_price = avg_price + (net_pr * cur_r);
SET remaind=remaind-cur_r;
UPDATE `people` SET `remains`=0 WHERE remains=cur_id;
END IF;
END LOOP;

CLOSE curs;


/*INSERT INTO `employees_audit`
SET ACTION = 'update',
employeeNumber = OLD.service_id,
lastname = OLD.halt_minutes,
chngedat = NOW(); */
END$$
DELIMITER ;

Options: ReplyQuote


Subject
Views
Written By
Posted
fetch result set using triggers
1184
June 16, 2016 06:27AM


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.