MySQL TRIGGER NEW.date=NOW() on any columns update except `id` and `name`

Question

This

CREATE TRIGGER pubdate BEFORE UPDATE ON `table1` FOR EACH ROW SET NEW.date=NOW()

on any column update in table would SET date=NOW().

How to add exception when updating columns id and name?

Update: basically run TRIGGER pubdate on update any table column except id and/or name.

So only update date iif either id or name change during an update? — Bohemian, Aug 17 '14 at 10:41
No, update date if updated any column except `id` or `date`. If updated just `id` and/or `date` then avoid trigger `pubdate`. — Binyamin, Aug 17 '14 at 10:53

score 1 · Answer 1 · answered Aug 17 '14 at 11:07

Mysql doesn't support column-based trigger actions, so your intention can't be coded at the action level of the trigger.

You can simulate the action in the logic of the rigger though by checking if any if the other columns have changed.

Assuming there are 3 other columns col1, col2, col3:

DELIMITER $$

CREATE TRIGGER pubdate BEFORE UPDATE ON `table`
FOR EACH ROW BEGIN
  IF NOT (NEW.col1 <=> OLD.col1
    AND NEW.col2 <=> OLD.col2 -- compare more other column as required
    AND NEW.col3 <=> OLD.col3) THEN 
      SET NEW.date = NOW();
  END IF;
END$$

DELIMITER ;

FYI the <=> operator is mysql's null-safe equals, which considers null <=> null to be true.

TJ- · Answer 2 · 2014-08-17T11:01:22.670

-1

If trigger is to be triggered when the id/name have not been touched:

DELIMITER $$

    CREATE TRIGGER pubdate BEFORE UPDATE ON `table1`
    FOR EACH ROW BEGIN
      IF (NEW.id = OLD.id and NEW.name = OLD.name) THEN -- Condition for updating
            SET NEW.date = NOW();
      END IF;
    END$$

DELIMITER ;

[Updated]

If the trigger is to be triggered when columns other than id/name have been touched (should be triggered when any_other_column and id/name are touched) as well:

DELIMITER $$

    CREATE TRIGGER pubdate BEFORE UPDATE ON `table1`
    FOR EACH ROW BEGIN
      IF (NEW.email <> OLD.email or NEW.other_column1 <> OLD.other_column1 ... ) THEN 
      -- Do this for all other columns in the table (except for id and name)
            SET NEW.date = NOW();
      END IF;
    END$$

DELIMITER ;

edited Aug 17 '14 at 11:01

answered Aug 17 '14 at 09:45

TJ-

14,085
12
59
90

You try to apply `date=NOW()` while updating just `id and `name`, but my question was opposite - apply `date=NOW()` for any row update except `id` and `name`. – Binyamin Aug 17 '14 at 10:04
1

Are you sure? When (old.id and new.id) are same AND (old.name and new.name) are same, that means there is no change to either of id and name (but other fields would have changed) and hence the new date can be changed. Isn't this what you are looking for? Also, because the question does not say what should be done if both id and some_other_field have been changed, what should be done. I have not handled that scenario but you can extrapolate this. – TJ- Aug 17 '14 at 10:08
So, if say, both id and some_column_foo are updated, teh trigger should not run. If that's the case, this solution works. – TJ- Aug 17 '14 at 10:18
@a_horse_with_no_name Isn't that the intention? If id & name (both) are unchanged, only and only then update the date? – TJ- Aug 17 '14 at 10:20
@TJ-, NO! See my code `FOR EACH ROW SET` - means, it can be any row. I want to add exception for `id` and `date`. If will be instantly updated rows `id` and `email`, then on updating `email` trigger `pubdate` must be affected, if instantly updated `id` and `date`, then to avoid `pubdate`. – Binyamin Aug 17 '14 at 10:23
id and name are `columns`. The row constitutes of (id, name, email, date, ...). When any update to this table happens (one column or multiple), this trigger will be triggered. The condition says that date will not be changed if there are any updates to id or name. I am not sure if I was able to understand your example correctly. – TJ- Aug 17 '14 at 10:31
Sorry I meant columns and not rows. The trigger `pubdate` must be affected on any column update except `id` and `name` for any `table` row. – Binyamin Aug 17 '14 at 10:45
@Binyamin: then TJ's solution is what you are looking for. – Aug 17 '14 at 10:46
@a_horse_with_no_name I think @ Binyamin is looking for 1 trigger across tables (table is not a particular table) that would do the job. – TJ- Aug 17 '14 at 10:49
No, just for one simple table. To avoid confusing, I renamed it to `table1`. I tested TJ's example, and as I thought it avoids trigger `pubdate` just if columns `id` and `name` are not updated with a different value, but it is not what I am asking. My question does not depend on value, but affected column. – Binyamin Aug 17 '14 at 10:56
Well, I guess the other option is to keep all of the other columns (other than the id and name) in the condition with the expressions being `or`-ed. I have updated my answer as well. – TJ- Aug 17 '14 at 11:02
I'm sure your second example would work fine. Unfortunately I do not want to mention in trigger all rows except `id` and `name` but opposite - in my case to mention just `id` and `name`. Is it possible at all? – Binyamin Aug 17 '14 at 11:07
1

You could explore [this](http://stackoverflow.com/questions/4950252/mysql-iterate-through-column-names/4951354#4951354) as a starting point. – TJ- Aug 17 '14 at 11:18

MySQL TRIGGER NEW.date=NOW() on any columns update except `id` and `name`

2 Answers2