我将一个设置了一个自增列,现在想在删除的时候保持序号的连贯性,所以用到了触发器
create trigger coursetri after delete on course
update course set courseID = courseID-1 where courseID>old.courseIDcourse表中有courseID 和courseName但是会出现错误啊!
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 'update course set courseID = courseID-1 where courseID>old.courseID' at line 1
create trigger coursetri after delete on course
update course set courseID = courseID-1 where courseID>old.courseIDcourse表中有courseID 和courseName但是会出现错误啊!
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 'update course set courseID = courseID-1 where courseID>old.courseID' at line 1
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
上面这个MYSQL中CREATE TRIGGER 的语法格式,你的FOR EACH ROW哪去了?MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
所以你所要求"在删除的时候保持序号的连贯性"的功能在MYSQL中无法通过触发器实现一般来说任何一款数据库设计中都不建议这种把号码改成连续的设计思路。一般来说主键不应该再被更改。如果你想显示连续的号码,则可以通过程序来实现或者通过SQL语句来实现。
我加上了,但是导致所有的都不能删除,请问是什么问题???
create trigger coursetri after delete on course for each row
update course set courseID = courseID-1 where courseID>old.courseID再次感谢各位