触mysql发器_Mysql触发器

mysql>DELIMITER|mysql>createtriggerset_recordafterinsertonscore->foreachrowbegin->set@studentid=new.student_id;->set@record=new.record;->set@score=new.score;->if@score>85...

mysql> DELIMITER |

mysql> create trigger set_record after insert on score

-> for each row begin

-> set @studentid=new.student_id;

-> set @record=new.record;

-> set @score=new.score;

-> if @score>85

-> update score set score.record=5 where student_id=new.student_id;

-> else if @score>70 then

-> update score set sc.record=4 where student_id=new.student_id;

-> else if @score>60 then

-> update score set score.record=3 where student_id=new.student_id;

-> else

-> update score set score.record=0 where student_id=new.student_id;

-> end if;

-> end;

-> |

ERROR 1064 (42000): 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 'updat

e score set score.record=5 where student_id=new.student_id;

else if @score>' at line 7

mysql> desc score;

+------------+-------------+------+-----+-------------------+-------+

| Field | Type | Null | Key | Default | Extra |

+------------+-------------+------+-----+-------------------+-------+

| score_id | varchar(10) | NO | PRI | NULL | |

| student_id | varchar(10) | NO | MUL | NULL | |

| course_id | varchar(10) | NO | MUL | NULL | |

| score | tinyint(4) | YES | | 0 | |

| record | tinyint(4) | YES | | 0 | |

| createtime | timestamp | YES | | CURRENT_TIMESTAMP | |

| score_term | varchar(30) | NO | | NULL | |

| grde | varchar(20) | NO | | NULL | |

+------------+-------------+------+-----+-------------------+-------+

8 rows in set (0.01 sec)

展开