0

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...

user3459444
  • 21
  • 3

1 Answers1

0

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

  • The table contains 150 fields so there are 149 watch and 1 not_watch fields. Unfortunately this is the way I've solved it so far, but that would be testing 149 conditions :( – user3459444 May 02 '22 at 12:56
  • You can test for it staying the same: `IF NEW.special_column = OLD.special_column THEN -do stuff-- END IF.` that way if it changes the code `--do stuff--` will not be run –  May 02 '22 at 13:03
  • If I not watch f3 only other 149 field: CREATE TRIGGER updtrigger BEFORE UPDATE ON mytable FOR EACH ROW BEGIN IF NEW.f0 <> OLD.f0 or NEW.f1 <> OLD.f1 or NEW.f2 <> OLD.f2 or NEW.f4 <> OLD.f4 or ...... NEW.f150 <> OLD.f150 THEN SET NEW.changed = 1; END IF; END If a new field is added to the table, it should also be added to the conditions. Instead, I am looking for a better solution. – user3459444 May 02 '22 at 13:15
  • I've tried to look it up. You could call a procedure which loops though all the columns of the table by querying the schema information table. As we don't generally add columns to a table often you're current system is more effective. You only write it once and then make a minor modification occasionally. –  May 02 '22 at 13:29