继触发器之后,引发的问题! Subquery returns more than 1 rows

之前写了一篇博客,写的如何去写触发器,下面是链接地址:

https://blog.csdn.net/choath/article/details/86512491

下面就是当时写的触发器:

报错信息:Subquery returns more than 1 rows

BEGIN    
declare phoneno varchar(20) character set utf8;
declare agencynameq varchar(100) character set utf8;
declare agencynameaa varchar(500) character set utf8;
declare phonenumber4 varchar(20) character set utf8;
declare phonenumber6 varchar(20) character set utf8;
IF new.state  in ('2','3')  then  

set agencynameq = (SELECT AgencyName   FROM ehome_green_agency_location WHERE TransNo =new.tranno);
SELECT phonenumber into phonenumber6 FROM sys_user WHERE id = (SELECT userid FROM sys_user_branch WHERE branch_code = 
(SELECT substring((SELECT AgencyCode FROM ehome_green_agency_location WHERE TransNo = new.tranno),1,6)));

SELECT phonenumber into phonenumber4 FROM sys_user WHERE id = (SELECT userid FROM sys_user_branch WHERE branch_code = 
(SELECT substring((SELECT AgencyCode FROM ehome_green_agency_location WHERE TransNo = new.tranno),1,4)));

SELECT idno into agencynameaa  FROM ehome_green_agency_location WHERE TransNo = new.tranno;

 if agencynameq is NULL  then 
set agencynameq = 'sa';
iNSERT INTO `userservice`.`ehome_us_green_message` (
    receivingPlatform,businesstype,serviceid,transno,wxstate,templateid,contents,description,updateflag,senddate,
    sendtype,sendtimes,createduser,createddate)
VALUES
    ('9','00','ceptrans0002',new.tranno,'0','AuditDX',CONCAT('Name:',agencynameq),'未查到审核人员手机号','4',sysdate(),
        '1','0','sys',sysdate());
      END if;
 if phonenumber6 is NOT NULL then 
     INSERT INTO `userservice`.`ehome_us_green_message` (
    receivingPlatform,businesstype,serviceid,transno,wxstate,templateid,contents,description,updateflag,senddate,
    sendtype,sendtimes,createduser,createddate)
VALUES
    ('3',phonenumber6,'ceptrans0002',new.tranno,'0','AuditDX',CONCAT('Name:',agencynameq) ,'待推送','0',sysdate(),
        '1','0','sys',sysdate());
elseif phonenumber4 is NOT NULL then 
INSERT INTO `userservice`.`ehome_us_green_message` (
    receivingPlatform,businesstype,serviceid,transno,wxstate,templateid,contents,description,updateflag,senddate,
    sendtype,sendtimes,createduser,createddate)
VALUES
    ('3',phonenumber4,'ceptrans0002',new.tranno,'0','AuditDX',CONCAT('Name:',agencynameq) ,'待推送','0',sysdate(),
        '1','0','sys',sysdate());
      END if;
END IF;  
END

上面这个触发器有1个问题?

该触发器的子查询很多,而且子查询的赋值是"=",存在自查询的情况下,用"="赋值,

可能出现子查询查出的结果不是一条,这样会出现一个"子查询的结果大于1的报错!"

这是你在修改相关表数据的时候报的,导致你无法修改表数据!!!

优化:取查询结果中的一条即可

备注:当然不光是触发器,在任何子查询中都可能遇到类似的情况!