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返回数据集。
亟待高人指路,谢谢。
解决方案 »
- MYsql 怎么约束列的取值范围
- MYsql里面如何复制一个表里面a字段的数据到b字段?
- mysql一个表字段名字是保留关键字,如何更新这个字段的内容
- mysql 插入数据编码问题
- 有谁知道怎么看my sql数据库表的字段名,我创建的表是一个空表
- 怎么样按某个字段统计表中有多少类不同的数据?
- win2000中配置数据源时怎么才能找到mysql的驱动程序啊?(小问题,解决就给分)
- MySQL使用Navicat工具写存储过程遇到问题
- 一个查询函数,帮忙看看哪里错了,输入数字时就可以查询,输入uuid时提示参数错误
- phpMyAdmin-4.0.10.8-all-languages版本登陆不上且无提示
- 关于mysql的一个简单问题
- 求update多条件更新语句
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?