mysql - 在mysql中使用select插入后创建触发器

Question

我正在尝试创建一个 mysql 触发器。错误代码 1064 非常通用。触发器是查看特定键是否存在记录；如果它确实使用该值更新新插入行中的特定列。我在触发器打开时在同一个表上使用选择查询。这会引起问题吗？这是代码:

DELIMITER $$

CREATE TRIGGER classid_insert
AFTER INSERT ON courses_semester
FOR EACH ROW
BEGIN
 SET @class_id = (SELECT class_id FROM courses_semester WHERE 
 course_id = NEW.`course_id`
AND `dept_id` = NEW.`dept_id`
AND `univ_id` = NEW.`univ_id`
AND `section_id` = NEW.`section_id`
AND `semester` = NEW.`semester`
AND `year` = NEW.`year`);
IF(@class_id = NULL)
THEN
 NEW.class_id = UUID()
ELSE
 NEW.class_id = @class_id
END IF;
END

DELIMITER ;

Answer 1

您的整个触发代码应如下所示。删除 DELIMITER $$ 后面和 DELIMITER ; 之前的空格。另外，最后一个 END 需要更改为 END $$。此外，它必须是 BEFORE 触发器，而不是 AFTER 触发器。

此外，NEW.class_id = UUID() 不正确。缺少 SET 作为

SET NEW.class_id = UUID();

更正的触发代码:

DELIMITER $$
CREATE TRIGGER classid_insert
BEFORE INSERT ON courses_semester
FOR EACH ROW
BEGIN
 SET @class_id = (SELECT class_id FROM courses_semester WHERE 
 course_id = NEW.`course_id`
AND `dept_id` = NEW.`dept_id`
AND `univ_id` = NEW.`univ_id`
AND `section_id` = NEW.`section_id`
AND `semester` = NEW.`semester`
AND `year` = NEW.`year`);
IF(@class_id = NULL)
THEN
 SET NEW.class_id = UUID();
ELSE
 SET NEW.class_id = @class_id;
END IF;
END $$
DELIMITER ;