MySQL Forums
Forum List  »  Source, Builds, Binaries

print effected row count with before delete trigger mysql
Posted by: Murali Krishna G
Date: December 03, 2015 02:38AM

when I work with the below code indivudually it is working good when started to integrate it give me

Error Code: 1415. Not allowed to return a result set from a trigger
My aim is to trigger before delete , which inserts the deleted rows into other table and Need to display the number of rows inserted.

delimiter |

CREATE TRIGGER deltab_before_del BEFORE delete ON dummytab
FOR EACH ROW
BEGIN
declare dept,cnt numeric;
DECLARE done INT DEFAULT FALSE;
declare firs VARCHAR(10);
declare las varchar(5);
DEClARE cur CURSOR FOR SELECT * FROM dummytab where deptId=OLD.deptId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open cur;
select found_rows() into cnt;
read_loop: LOOP
Fetch cur into dept,firs,las;
IF done THEN
LEAVE read_loop;
END IF;
INSERT INTO deltab set deptId1=dept,firstName1=firs,
lastname1=las;
END LOOP;
close cur;
call cntr(cnt);
END;
|

delimiter ;
and procedure is

delimiter //
CREATE PROCEDURE cntr(cnt INT)
BEGIN
select concat("number of rows effected",cnt) as count;
END;
//
Dummytab = is the actual table I want to delete rows
deltab = is the table I want to insert before delete
Can Anyone help me with the code.

and give me suggestions to simplyfy the above code with other methods.

Thanks a lot in advance.

Options: ReplyQuote


Subject
Views
Written By
Posted
print effected row count with before delete trigger mysql
1619
December 03, 2015 02:38AM


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.