mysql触发器怎么写 [英] how to write mysql trigger
问题描述
有两个表
表A
there are two tables
table A
如果用户使用此查询插入A值(abc,1,50);"
if user use this query "insert into A values(abc,1,50);"
然后,触发器应检查表B"中的"student_name",如果表B"中已存在"student_name",则 更新表B中的"all_marks"字段,例如"all_marks"应为60
then trigger should check student_name in "Table B" if student_name is already there in "table B" then update "all_marks"field in table B eg., "all_marks" should be 60
如果表B"中没有"student_name""abc",则应将相同的"student_name","student_class","student_marks"插入表B
if student_name "abc" is not there in "table B" then same student_name ,student_class, student_marks should be inserted into table B
delimiter $$
create trigger insert_into_summary
after insert on A
for each row
begin
if
//** here i want to write the if condition **
insert into B (name,class,overall_marks)
values(new.name,new.class,new.marks,);
else
update B set all_marks=old.all_marks+new.student_marks;
end $$
delimiter ;
请帮助我编写触发器.
推荐答案
You can simply this by using On Duplicate Key Update . Add a unique key on student name / ID then use the following code in your trigger
begin
insert into summary_score (name,number,all_marks)
values(new.name,new.marks,new.score)
on duplicate key update all_marks=old.all_marks+new.student_marks
where B.Name=new.Name;
end $$
这篇关于mysql触发器怎么写的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!