Search code examples
mysqltriggersnull

SQL Trigger sum rows when some value is NULL

I want to sum 3 rows but when value1 is NULL i want to skip it to sum all other rows:

CREATE TRIGGER `sum_total_test_before_insert` BEFORE INSERT ON `test`  
FOR EACH ROW  
BEGIN  
 IF (NEW.value1 IS NULL) THEN  
 SET NEW.Total = (NEW.value2 + NEW.value3);  
 ELSE  
 SET NEW.Total = (NEW.value1 + NEW.value2 + NEW.value3);  
END IF;

Example of Expected values on Total row

value1  value2  value3  Total
5       6       6       17
2       5       4       11
NULL    5       4       9

ERROR: #1064 - Something is wrong in your syntax near '' in line 5

Thanks for your help.

I expect to have a correct syntax

Solution 
  • CREATE TRIGGER `sum_total_test_before_insert` 
BEFORE INSERT ON `test`  
FOR EACH ROW  
SET NEW.Total = COALESCE(NEW.value1, 0) + NEW.value2 + NEW.value3;

    But I'd prefer to make Total column generated one. See https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html