MySQL Trigger question: only trigger when a column is changed?
33,921
Solution 1
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
//
Solution 2
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;
$$
Author by
kylex
Updated on July 17, 2020Comments
-
kylex almost 4 years
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 thedate
column, but ONLY if theactive
column gets changed.If other columns are changed but not the
active
column, then the date would remain the same.