Lorenzo Merici Lorenzo Merici - 6 months ago 13
SQL Question

MariaDB trigger, autoupdate field on edit?

I have a DB based on Maria DB and a table "values" with two fields (for example): "X" and "Y".
I want to create a trigger that, on insert and on update of X, makes Y = X / 10.

I've tried something like this:

CREATE TRIGGER my_table_trig AFTER UPDATE OF X ON values
BEGIN
update values SET values.Y = X/10 WHERE X = NEW.X;
END;


But obviously it is not working. How i can do that?

Thanks, Lorenzo

Answer

You had numerous problems. This should be closer

CREATE TRIGGER my_table_trig BEFORE UPDATE ON `values`
FOR EACH ROW
     SET NEW.Y = OLD.X/10;
CREATE TRIGGER my_table_trig BEFORE INSERT ON `values`
FOR EACH ROW
     SET NEW.Y = OLD.X/10;

Unclear: If X did not change, do you still want Y to be set to X/10?

Note BEFORE, NEW and OLD, using SET, not UPDATE, 2 triggers, etc.

Comments