Error in My SQL Syntax for trigger creation or execution

value_table

• id auto increment not null unique
• primary_object_id
• user_id
• value

group_table

• id auto increment not null unique
• primary_object_id
• 1
• 2
• 3
• 4
• 5
• 6
• 7

I have two tables one stores values(value_table) and other(group_value) stores values count. There are about 7 numeric columns in group_table 1 to 7.

Each user can give a value between 1 to 7 to the object who has id=primary_object_id and can also update the same, So I need to keep track how many users have given 1 to different objects with their respective primary_object_id

Now what I am trying to achieve is if their is update on value_table say initially 4 to final 7 then I want to update group_value table and increment count of column with name 7 by 1, and decrement count of column with name 4

I have came to this SQL but it gives syntax error

I know there is the error only in those two consecutive statements, because I tried static update statement like

update group_value set `7` = `7` + 1 where id=OLD.primary_object_id;;

The above statement works. But How I could I use the old values and new values to update value in group_value table

DELIMITER |

  CREATE TRIGGER UpdateCount AFTER UPDATE ON value_table FOR EACH ROW 

  BEGIN
      IF OLD.value <> NEW.value THEN
        UPDATE group_value SET `OLD.value` = `OLD.value` - 1 where id=OLD.primary_object_id;
        UPDATE group_value SET `NEW.value` = `NEW.value` + 1 where id=OLD.primary_object_id;
      END IF;
  END;
|
DELIMITER ;

Answer

Table alias and column alias must be quoted separately.

Not

UPDATE group_value SET `OLD.value` = `OLD.value` - 1 where id=OLD.id;

but

UPDATE group_value SET `OLD`.`value` = `OLD`.`value` - 1 where id=OLD.id;

---

if user updates his value in value_table from 4 to 5 then I need to reduce count of 4 by 1 and increment count of 5 by 1 in group_value tab

The task is to use the column’s value as the name of the column? This needs in prepared statement but it is not allowed in triggers.

The only solution may be:

CREATE TRIGGER UpdateCount 
AFTER UPDATE 
ON value_table 
FOR EACH ROW 
BEGIN
    CASE OLD.value 
        WHEN 1 
        THEN UPDATE group_value SET `1` = `1` - 1 WHERE id=OLD.primary_object_id;
        WHEN 2 
        THEN UPDATE group_value SET `2` = `2` - 1 WHERE id=OLD.primary_object_id;
        WHEN 3 
        THEN UPDATE group_value SET `3` = `3` - 1 WHERE id=OLD.primary_object_id;
        WHEN 4 
        THEN UPDATE group_value SET `4` = `4` - 1 WHERE id=OLD.primary_object_id;
        WHEN 5 
        THEN UPDATE group_value SET `5` = `5` - 1 WHERE id=OLD.primary_object_id;
        WHEN 6 
        THEN UPDATE group_value SET `6` = `6` - 1 WHERE id=OLD.primary_object_id;
        WHEN 7 
        THEN UPDATE group_value SET `7` = `7` - 1 WHERE id=OLD.primary_object_id;
    END CASE;
    CASE NEW.value 
        WHEN 1 
        THEN UPDATE group_value SET `1` = `1` + 1 WHERE id=NEW.primary_object_id;
        WHEN 2 
        THEN UPDATE group_value SET `2` = `2` + 1 WHERE id=NEW.primary_object_id;
        WHEN 3 
        THEN UPDATE group_value SET `3` = `3` + 1 WHERE id=NEW.primary_object_id;
        WHEN 4 
        THEN UPDATE group_value SET `4` = `4` + 1 WHERE id=NEW.primary_object_id;
        WHEN 5 
        THEN UPDATE group_value SET `5` = `5` + 1 WHERE id=NEW.primary_object_id;
        WHEN 6 
        THEN UPDATE group_value SET `6` = `6` + 1 WHERE id=NEW.primary_object_id;
        WHEN 7 
        THEN UPDATE group_value SET `7` = `7` + 1 WHERE id=NEW.primary_object_id;
    END CASE;
END

But I strongly recommend to normalize the data.