CREATE
TRIGGER `tr_distributeMail` AFTER INSERT ON `邮件接收者表`
FOR EACH ROW BEGIN
declare ulMailId INTEGER;
IF EXISTS (SELECT `待发邮件编号` from `邮件接收者表` WHERE `开始转发` = 1) then
select ulMailId = `待发邮件编号` from `邮件接收者表` where `开始转发` = 1;
call sp_distributeMail(ulMailId);
end if;
END;错误如下
Error Code : 1415
Not allowed to return a result set from a trigger将第六行select...去掉则成功,说明不能用select返回数据集。
亟待高人指路,谢谢。
TRIGGER `tr_distributeMail` AFTER INSERT ON `邮件接收者表`
FOR EACH ROW BEGIN
declare ulMailId INTEGER;
IF EXISTS (SELECT `待发邮件编号` from `邮件接收者表` WHERE `开始转发` = 1) then
select ulMailId = `待发邮件编号` from `邮件接收者表` where `开始转发` = 1;
call sp_distributeMail(ulMailId);
end if;
END;错误如下
Error Code : 1415
Not allowed to return a result set from a trigger将第六行select...去掉则成功,说明不能用select返回数据集。
亟待高人指路,谢谢。
TRIGGER `tr_distributeMail` AFTER INSERT ON `学生成绩表`
FOR EACH ROW
BEGIN
call sp_distribute();
END;
-----------------------------------------------------------------
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_distribute`()
BEGIN
declare ulSId INTEGER;
select ulSId = `学生学号` from `学生成绩表` where `确认信息` = 1;
insert into `成绩公布表`(`学生学号`) values (ulSId);
END;
-------------------------------------------------------------------
新建查询如下:
set FOREIGN_KEY_CHECKS = 0;
insert 学生成绩表 values (1,101,16,1);
'序号','学生学号','年龄','确认信息'
---------------------------------------------------------------------
执行结果:
(0 row(s) affected)
Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000Error Code : 1415
Not allowed to return a result set from a triggerExecution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000
select ulSId = `学生学号` from `学生成绩表` where `确认信息` = 1; insert into `成绩公布表`(`学生学号`)
select 学生学号 from `学生成绩表` where `确认信息` = 1;
But how could the resolution be?
A record should be insert into table B after the Table A added a new record, and query sentences surely appeare in the sproc. I use the trigger which call the sproc. Note that the error take appeared, what is the resolution?