u_user.UserID in (new.TaskHandler)改成find_in_set(u_user.UserID, new.TaskHandler) > 0 u_user.UserID in (new.TaskHandler) 这个写法是错误的你这个效果在执行的时候等同于 select 1 in (’1,2,3,4‘) 注意里面的是‘1,2,3,4’ 是一个元素,而你要的效果应该是select 1 in (1,2,3,4)
CREATE DEFINER=`root`@`%` TRIGGER `t_task_AINS` AFTER INSERT ON `t_task` FOR EACH ROW BEGIN declare sname varchar(50); declare semail varchar(50); declare tempname varchar(50); declare done int; declare curstaff cursor for (select StaffName,Email from u_user left join u_staff on u_user.StaffID=u_staff.StaffID where find_in_set(u_user.UserID, new.TaskHandler)); declare continue handler for sqlstate '02000' set done=1; if new.TaskHandler is not null and new.TaskHandler<>'' then select TemplateName into tempname from t_tasktemplate where TemplateID=new.TemplateID; open curstaff; set done=0; repeat fetch curstaff into sname,semail; if done=0 then insert into t_sms (sms_dest ,sms_createtime ,sms_content ,sms_type ,sms_flag ,sms_del) values(semail ,sysdate() ,concat('您好,',sname,'。您在[XXX]中有任务“',tempname,'”需要处理,请登录系统处理该任务。') ,1 ,1 ,2); end if; until done=1 end repeat; close curstaff; end if; END
用动态执行的方法试试
CREATE DEFINER=`root`@`%` TRIGGER `t_task_AINS` AFTER INSERT ON `t_task` FOR EACH ROW
BEGIN
declare sname varchar(50);
declare semail varchar(50);
declare tempname varchar(50);
declare done int;
declare curstaff cursor for
(select StaffName,Email
from u_user
left join u_staff on u_user.StaffID=u_staff.StaffID
where find_in_set(u_user.UserID, new.TaskHandler));
declare continue handler for sqlstate '02000' set done=1;
if new.TaskHandler is not null and new.TaskHandler<>'' then
select TemplateName into tempname from t_tasktemplate where TemplateID=new.TemplateID;
open curstaff;
set done=0;
repeat
fetch curstaff into sname,semail;
if done=0 then
insert into t_sms (sms_dest
,sms_createtime
,sms_content
,sms_type
,sms_flag
,sms_del)
values(semail
,sysdate()
,concat('您好,',sname,'。您在[XXX]中有任务“',tempname,'”需要处理,请登录系统处理该任务。')
,1
,1
,2);
end if;
until done=1
end repeat;
close curstaff;
end if;
END