Q:

trigger to update values of another table according to input of first table

Create or replace Trigger t1 AFTER INSERT ON feedback    
FOR EACH ROW    
BEGIN    
IF new.rating = 1 THEN update count SET count=count+1  WHERE scale="one";    
ELSE IF new.rating = 2 THEN UPDATE count SET count=count+1 WHERE scale="two";    
ELSE IF new.rating = 3 THEN UPDATE count SET count=count+1 WHERE scale="three";    
ELSE IF new.rating = 4 THEN UPDATE count SET count=count+1 WHERE scale="four";    
ELSE IF new.rating = 5 THEN UPDATE count SET count=count+1 WHERE scale="five";    
END IF;    
END
//

table feedback
table count
I want to write trigger which update value of count in table count according to new insert values in feedback.
e.g. if I insert value of rating as 3 in 'feedback' table then it will automatically update value of count of 'three' in 'count table' by 1.And suppose if I insert value of rating as 5 in 'feedback' table then it will automatically update value of count of 'five' in 'count table' by 1.
syntax error in above code

A:

Here trigger is written on feedback table and inside trigger value of counter updating which is in another count table so that is not possible. when you write trigger you should update values of columns present in table on which trigger is written only. so here first we should write procedure and call it from trigger.
procedure:
create procedure c(cnt int(10))
begin
DECLARE rating_temp int (20);
DECLARE cur CURSOR FOR SELECT cnt;
OPEN cur;
FETCH cur INTO rating_temp;
IF rating_temp=5 then
update count1 SET counter=(counter+1) where scale="five";
ELSEIF rating_temp=4 then
update count1 SET counter=(counter+1) where scale="four";
ELSEIF rating_temp=3 then
update count1 SET counter=(counter+1) where scale="three";
ELSEIF rating_temp=2 then
update count1 SET counter=(counter+1) where scale="two";
ELSEIF rating_temp=1 then
update count1 SET counter=(counter+1) where scale="one";
END IF;
CLOSE cur;
END$$

2) trigger-
CREATE TRIGGER t1
AFTER INSERT
ON feedback
FOR EACH ROW
BEGIN
Declare cnt2 int(10);
select new.rating into cnt2;
call c(cnt2);
END$$

