这是我尝试这写的。第一次写的 course(course_id,course_name,course_credit); grade(credit course_id,student_id,grade);但是为什么 我插入成绩的时候没有跟新我成绩里面的学分呢?create trigger s1 AFTER insert on grade FOR EACH ROW BEGIN update grade set new.credit= case when new.grade>85 then course.course_credit when new.grade BETWEEN 60 and 85 then (course.course_credit*0.85) when new.grade<60 then course.course_credit*0.5 end WHERE new.course_id=course.course_id; end
create trigger s1 AFTER insert on grade FOR EACH ROW BEGIN set new.credit= case when new.grade>85 then course.course_credit when new.grade BETWEEN 60 and 85 then (course.course_credit*0.85) when new.grade<60 then course.course_credit*0.5 end ;
create table course ( course_id varchar(30) primary key , course_name varchar(50) not null, course_credit double(3,1) );create table grade ( credit double(3,1), course_id varchar(30), student_id varchar(30), student_grade int, PRIMARY key(course_id,student_id), foreign key (course_id) REFERENCES course(course_id) );create trigger s1 AFTER insert on grade FOR EACH ROW BEGIN update grade set new.credit= case when new.student_grade>85 then course.course_credit when new.student_grade BETWEEN 60 and 85 then (course.course_credit*0.85) when new.student_grade<60 then course.course_credit*0.5 end WHERE new.course_id=course.course_id; end insert into course VALUES (1,'数据库', 4.0);insert into course VALUES (2,'程序设计',5.0);下面2条语句插不进去。因为触发器的原因 insert INTO grade (course_id,student_id,student_grade) values (1,2008,84);insert INTO grade (course_id,student_id,student_grade) values (2,2008,60);
mysql> create trigger s1 -> before insert on grade -> FOR EACH ROW -> set NEW.credit = (case -> when new.student_grade>85 then 1 -> when new.student_grade BETWEEN 60 and 85 then 0.85 -> when new.student_grade<60 then 0.5 -> end) * (select course_credit from course where course_id=new.course_id) ; Query OK, 0 rows affected (0.03 sec)mysql> insert INTO grade (course_id,student_id,student_grade) -> values (1,2008,84); Query OK, 1 row affected (0.14 sec)mysql> mysql> insert INTO grade (course_id,student_id,student_grade) -> values (2,2008,60); Query OK, 1 row affected (0.05 sec)mysql> select * from grade; +--------+-----------+------------+---------------+ | credit | course_id | student_id | student_grade | +--------+-----------+------------+---------------+ | 3.4 | 1 | 2008 | 84 | | 4.2 | 2 | 2008 | 60 | +--------+-----------+------------+---------------+ 2 rows in set (0.00 sec)mysql>
a 85
b 70
c 60
course(course_id,course_name,course_credit);
grade(credit course_id,student_id,grade);但是为什么 我插入成绩的时候没有跟新我成绩里面的学分呢?create trigger s1
AFTER insert on grade
FOR EACH ROW BEGIN
update grade set new.credit=
case
when new.grade>85 then course.course_credit
when new.grade BETWEEN 60 and 85 then (course.course_credit*0.85)
when new.grade<60 then course.course_credit*0.5
end
WHERE new.course_id=course.course_id;
end
AFTER insert on grade
FOR EACH ROW BEGIN
set new.credit=
case
when new.grade>85 then course.course_credit
when new.grade BETWEEN 60 and 85 then (course.course_credit*0.85)
when new.grade<60 then course.course_credit*0.5
end ;
end
如果少了WHERE new.course_id=course.course_id;
你这样就没找到是哪门课程的学分了。
不同课程的学分不同
create table course
(
course_id varchar(30) primary key ,
course_name varchar(50) not null,
course_credit double(3,1)
);create table grade
(
credit double(3,1),
course_id varchar(30),
student_id varchar(30),
student_grade int,
PRIMARY key(course_id,student_id),
foreign key (course_id) REFERENCES course(course_id)
);create trigger s1
AFTER insert on grade
FOR EACH ROW BEGIN
update grade set new.credit=
case
when new.student_grade>85 then course.course_credit
when new.student_grade BETWEEN 60 and 85 then (course.course_credit*0.85)
when new.student_grade<60 then course.course_credit*0.5
end
WHERE new.course_id=course.course_id;
end insert into course
VALUES (1,'数据库', 4.0);insert into course
VALUES (2,'程序设计',5.0);下面2条语句插不进去。因为触发器的原因
insert INTO grade (course_id,student_id,student_grade)
values (1,2008,84);insert INTO grade (course_id,student_id,student_grade)
values (2,2008,60);
-> before insert on grade
-> FOR EACH ROW
-> set NEW.credit = (case
-> when new.student_grade>85 then 1
-> when new.student_grade BETWEEN 60 and 85 then 0.85
-> when new.student_grade<60 then 0.5
-> end) * (select course_credit from course where course_id=new.course_id) ;
Query OK, 0 rows affected (0.03 sec)mysql> insert INTO grade (course_id,student_id,student_grade)
-> values (1,2008,84);
Query OK, 1 row affected (0.14 sec)mysql>
mysql> insert INTO grade (course_id,student_id,student_grade)
-> values (2,2008,60);
Query OK, 1 row affected (0.05 sec)mysql> select * from grade;
+--------+-----------+------------+---------------+
| credit | course_id | student_id | student_grade |
+--------+-----------+------------+---------------+
| 3.4 | 1 | 2008 | 84 |
| 4.2 | 2 | 2008 | 60 |
+--------+-----------+------------+---------------+
2 rows in set (0.00 sec)mysql>
非常谢谢了。但是还有一个小地方不懂,就是我是插入数据后才执行触发器的。为什么是before呢