MySQL Trigger question: only trigger when a column

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.

Answer 1

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

Answer 2

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