MySQL触发器,一直显示语法错误,说分号不对?

LauZyHou
  • 43
create trigger trigger_course
after insert on course_course for each row
begin
    declare sid varchar(8);
    declare sname varchar(20);
    select logic_id into sid from course_selectcourse where id=new.course;
    select name into sname from course_selectcourse where id=new.course;
    update course_course
    set sub_logic_id=sid, sub_name=sname
    where id=new.id;
end;

22:28:06 create trigger trigger_course after insert on course_course for each row begin declare sid varchar(8) Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4 0.000 sec


一方面是已采纳的老哥说的那个问题,另一方面是update循环触发触发器也有问题。
记录一下正确写法:

drop trigger if exists trigger_course;
delimiter $$
create trigger trigger_course
before insert on course_course for each row
begin
    declare sid varchar(8);
    declare sname varchar(20);
    select logic_id into sid from subject_subject where id=new.subject_id;
    select name into sname from subject_subject where id=new.subject_id;
    set new.sub_logic_id=sid, new.sub_name=sname;
end;
$$
回复
阅读 1.6k
1 个回答
✓ 已被采纳

定义过程函数和触发器等要先重新定义分隔符
delimiter $$, 这是因为分号此时并不是语义的结束, 而只是你要定义的实体的一部分.

试试下面的代码

delimiter $$

create trigger trigger_course
after insert on course_course for each row
begin
    declare sid varchar(8);
    declare sname varchar(20);
    select logic_id into sid from course_selectcourse where id=new.course;
    select name into sname from course_selectcourse where id=new.course;
    update course_course
    set sub_logic_id=sid, sub_name=sname
    where id=new.id;
end;
$$
撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
宣传栏