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.
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
//
SET NEW.date = CURDATE(); ?
<=> instead of <> ?
<=> 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.activeRan 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;
$$
NOT operator instead of comparing to zero, as in the following answer: stackoverflow.com/a/24041832/1419007. Otherwise, good answer.
END before the final $$?
