Mysql trigger if only x field changed

Question

I want to check in a mysql event (onupdate) if only a specific field has changed. For example: only x field value has changed? Then do nothing. The problem is that the table contains about 150 fields...

Answer 1

We can create a trigger which compares the old and new values of one column and then only perform the action if it has changed, or any test on any combination of columsn that we want.

create table mytable(
  watch int,
  not_watch int,
  safeguard int );

?

 CREATE TRIGGER updtrigger BEFORE UPDATE ON mytable
     FOR EACH ROW
     BEGIN
     IF NEW.watch <> OLD.watch THEN
     SET NEW.safeguard = new.watch;     
     END IF;
     END

?

insert into mytable values (25,20,0);

?

update mytable set watch = 50;

?

select * from mytable;

watch | not_watch | safeguard
----: | --------: | --------:
   50 |        20 |        50

update mytable set watch = 75;

?

select * from mytable;

watch | not_watch | safeguard
----: | --------: | --------:
   75 |        20 |        75

update mytable set not_watch = 100;

?

select * from mytable;

watch | not_watch | safeguard
----: | --------: | --------:
   75 |       100 |        75

db<>fiddle here