Mysql说: #1422 - Explicit or implicit commit is not allowed in stored function or trigger

Question

在下面的代码中，我尝试创建一个触发器，如果​​ Web 应用程序的用户没有上传属于“incidentreport”表的表单的图像，则该触发器会插入“incidentimg”表 id 以及图像路径，我只收到错误“MySQL said: #1422 - 存储函数或触发器中不允许显式或隐式提交”

DROP TRIGGER IF EXISTS `insertImage`;
CREATE TRIGGER `insertImage` AFTER INSERT ON `incidentreport` 
FOR EACH ROW 
BEGIN 
    IF(SELECT incidentimg.incidentImgId FROM incidentimg 
       LEFT JOIN incidentreport ON incidentimg.imgs_incidentReportId = incidentreport.incidentReportId 
       WHERE incidentimg.incidentImgId IS NULL) 
       THEN INSERT INTO incidentimg(incidentImgId, imgUrl)values(NEW.incidentReportId, 'images/no-image.png'); 
    END IF; 
END
;

这是正在使用的表格

有人可以帮助我解决这个问题

Answer 1

我终于让代码可以工作了。现在，代码将检查“incidentimg”表是否具有“incidentreport”表所具有的所有“incidentReportId”，如果没有，则将插入“incidentreport”表中最后插入的 id 以及默认图像路径插入后进入“incidentimg”表。

CREATE TRIGGER insertImage AFTER INSERT ON incidentreport
FOR EACH ROW    
BEGIN
    IF(SELECT incidentreport.incidentReportId 
        FROM incidentreport
        LEFT JOIN incidentimg ON incidentreport.incidentReportId = incidentimg.imgs_incidentReportId
        WHERE incidentimg.imgs_incidentReportId IS NULL)
        THEN INSERT INTO incidentimg(imgs_incidentReportId, imgUrl) VALUES(NEW.incidentReportId, 'images/no-image.png');
    END IF;
END