MySQL Trigger question: only trigger when a column is changed?

Question

I don't know if this is possible, but I have a column named active in a table. Whenever the active column gets changed, I would like to reset the date in the date column, but ONLY if the active column gets changed.

If other columns are changed but not the active column, then the date would remain the same.

before update need to check the old value to the new value of active column , see example in http://www.java2s.com/Code/Oracle/Trigger/ReferenceoldandnewvaluebycolumninabeforeupdateTrigger.htm — Haim Evgi, Nov 04 '10 at 15:06
http://stackoverflow.com/questions/6296313/mysql-trigger-after-update-only-if-row-has-changed — zloctb, Nov 20 '14 at 11:07

score 41 · Accepted Answer · answered Nov 04 '10 at 15:13

41

something like

DELIMITER //
 CREATE TRIGGER updtrigger BEFORE UPDATE ON mytable
     FOR EACH ROW
     BEGIN
     IF NEW.active <> OLD.active THEN
     SET NEW.date = '';     
     END IF;
     END
     //

answered Nov 04 '10 at 15:13

Haim Evgi

123,187
45
217
223

If I want to update the date to current date would the line change to `SET NEW.date = CURDATE();` ? – kylex Nov 04 '10 at 15:56
4

if you want date yes, if you want date+time use NOW() – Haim Evgi Nov 05 '10 at 11:49
1

Had you **set active = 1** while active was already 1 in the DB then wouldn't that be a change? I guess technically it would, because you have set the column and in database terminology it's a column change. But **NEW.active is not OLD.active** gives you only if NEW and OLD had different values! What will you do in that case? @HaimEvgi – edam Aug 12 '15 at 04:50
1

Isn't it better to use `<=>` instead of `<>` ? – stack Sep 07 '16 at 04:40
2

@stack yes, although not literally, `<=>` means "equal to, where `null` is treated like a value that can be compared", so you'd need to change to `not NEW.active <=> OLD.active` – Brian Leishman Feb 06 '18 at 21:45

score 19 · Answer 2 · edited Jul 17 '13 at 05:18

19

Ran into an issue with the IF test in the #2 example. When one of the values is null the <> test returns null. This leads to the test not getting met and the action of the trigger will not get run even though the one value does not equal null at all. To fix this I came up with this test that uses <=> (NULL-safe equal). Hope this helps someone out.

DELIMITER $$
DROP TRIGGER IF EXISTS updtrigger ;
$$
CREATE TRIGGER updtrigger  AFTER UPDATE
    ON yourTable FOR EACH ROW
BEGIN
    IF ((NEW.active <=> OLD.active) = 0)  THEN
     SET NEW.date = '';     
     END IF;
$$

edited Jul 17 '13 at 05:18

Frungi

506
5
16

answered Feb 12 '13 at 02:52

John Glassman

311
2
6

5

You can simply use the `NOT` operator instead of comparing to zero, as in the following answer: http://stackoverflow.com/a/24041832/1419007. Otherwise, good answer. – user2428118 Jun 06 '14 at 08:19
1

Shouldn't this have `END` before the final `$$`? – Ricky McMaster Jan 09 '19 at 14:52
@Johnglassman - why not use BEFORE instead of AFTER? – Valter Ekholm Aug 15 '20 at 15:01
@ValterEkholm Was a long time ago, but I suspect that my own use case was an after trigger and it was not the relevant aspect of my post. I would bet the test behaves the same way regardless of the trigger action. – John Glassman Nov 08 '20 at 01:36
@JohnGlassman Ok, you seem to remember quite well after all years. I have a memory of testing "after" and when it didn't work, I posted this/that comment, but it did work (in my use case, as I remeber) when using "before". Thanks – Valter Ekholm Nov 08 '20 at 21:25

MySQL Trigger question: only trigger when a column is changed?

2 Answers2

Linked

Related