CODEWITHSUNDEEP
mysqltriggers
kylex
kylex

Reputation: 14406

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

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.

Upvotes: 25

Views: 38335

Answers (2)

John Glassman
John Glassman

Reputation: 311

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;
$$

Upvotes: 19

Haim Evgi
Haim Evgi

Reputation: 125604

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

Upvotes: 41

Related Questions