Search code examples
mysqlsqltriggerseventtrigger

trigger error to update a column with the sum of 2 other column

I have a simple table called 'followers':

id fb tw sum 
1   2  4  
2   6  5
3   4  8

I want to create a trigger such that after inserting data into the 'fb' and 'tw' columns, the fourth colum 'sum' will be the sum of fb+tw.

Here is my code for the trigger:

USE `my_database`;
DELIMITER $$
CREATE TRIGGER `followers_AINS` AFTER INSERT ON `followers` FOR EACH ROW 
BEGIN
UPDATE sum SET sum=fb+tw
END
DELIMITER;

I keep getting a DDL error.

Solution

  • Use a before trigger. And set the values with the NEW keyword to indicate the currently inserted record

    DELIMITER $$
CREATE TRIGGER `followers_AINS` BEFORE INSERT ON `followers` 
FOR EACH ROW 
BEGIN
  SET NEW.sum = NEW.fb + NEW.tw;
END
$$
DELIMITER ;

    Also use a space between delimiter and the actual delimiter to change the definition.