HeidiSQL Before Update trigger error when manually modifying updated column value

Question

BEGIN
    SET NEW.value_result = NEW.value_a + NEW.value_b;
END

I'm trying to understand why if I manually modify the value_result column is showing the following error:

MariaDB: Error 0 rows updated when that should have been 1.

If I modify value_a or value_b columns manually there's no issue and value_result column is updated perfectly. But if by "accident" I modify the value_result column the error is shown.

Can this be prevented? By manually i mean using HeidiSQL interface and not query code.

All columns are INT(11)

I'm a bit lost on the question and why you aren't just using `SET NEW.value_result = NEW.value_a + NEW.value_b`. — Gordon Linoff, Jan 23 '21 at 17:17
Corrected, what i want is that if i modify the column value_result using the interface, the error not to be shown and the result stay the same as the trigger. Let's say value_a = 5 and value_b = 3 trigger makes value_result = 8. If i modify manually 8 to 14 the error is shown. — LUE PLAYER, Jan 23 '21 at 17:34

score 1 · Accepted Answer · answered Jan 23 '21 at 18:14

MySQL and MariaDB only update rows where values are changed. Here is an example in db<>fiddle.

I suspect this is the issue that you are seeing.

If you do:

update t
    set value_a = 123
    where id = ?;

Then (presumably) both value_a and value_result change. The row is updated.

If you do:

update t
    set value_result = 123
    where id = ?;

Then the trigger resets value_result to the old value and no rows are changed.

Note: It sounds like you would be better off with a computed column rather than setting a value in a trigger:

alter table t add value_result int as (value_a + value_b);

The database should not even let you try to assign a value to a computed column -- and the value is always correct.

HeidiSQL Before Update trigger error when manually modifying updated column value

1 Answers1