sql - 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)

question from:https://stackoverflow.com/questions/65862077/heidisql-before-update-trigger-error-when-manually-modifying-updated-column-valu

Answer 1

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.