Question

mysql delete型触发器使用出错

CREATE TRIGGER teacher_cancel AFTER DELETE ON teacher_info
FOR EACH ROW BEGIN
IF old.teacher_id IN (select teacher_id from teach_schedule) THEN
INSERT INTO teacher_info
VALUES(old.teacher_id,old.name,old.gender,old.age,old.tech_title,old.telephone,old.salary)
END IF
END
出现错误
error : 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 'END IF
END' at line 6
谁能帮我修改下吗？
mysql> CREATE TRIGGER teacher_cancel AFTER DELETE ON teacher_info
FOR EACH ROW
BEGIN
IF old.teacher_id IN (select teacher_id from teach_schedule) THEN
INSERT INTO teacher_info
VALUES(old.teacher_id,old.name,old.gender,old.age,old.tech_title,old.telephone,old.salary)
END IF;
END;
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 'END IF;
END' at line 7
mysql>
....还是不行
原因好像是old,teacher_id识别不了

Answer 1

delimiter //

CREATE TRIGGER teacher_cancel AFTER DELETE ON teacher_info
FOR EACH ROW BEGIN
IF old.teacher_id IN (select teacher_id from teach_schedule) THEN
INSERT INTO teacher_info
VALUES(old.teacher_id,old.name,old.gender,old.age,old.tech_title,old.telephone,old.salary);
END IF;
END ;//

delimiter ;

Answer 2

CREATE TRIGGER teacher_cancel AFTER DELETE ON teacher_info
FOR EACH ROW
BEGIN
IF old.teacher_id IN (select teacher_id from teach_schedule) THEN
INSERT INTO teacher_info
VALUES(old.teacher_id,old.name,old.gender,old.age,old.tech_title,old.telephone,old.salary)
END IF;
END;
这样呢？