CODEWITHSUNDEEP
mysqlsqltriggers
usayee
usayee

Reputation: 59

how to write mysql trigger


there are two tables
table A

if user use this query "insert into A values(abc,1,50);"

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

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 ;

please help me how to write trigger.

Upvotes: 2

Views: 938

Answers (2)

Biju Soman
Biju Soman

Reputation: 438

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 $$

Upvotes: 1

Amit Singh
Amit Singh

Reputation: 8109

try like this...

if (Select Count(Name) from B where B.Name=new.name)<1 
insert into summary_score (name,number,all_marks)
values(new.name,new.marks,new.score);
else 
update B set all_marks=old.all_marks+new.student_marks where B.Name=new.Name;

Upvotes: 1

Related Questions