Mysql current date trigger

I have the following trigger

CREATE TRIGGER `qc_date_trigger` AFTER UPDATE ON `brand`  FOR EACH ROW BEGIN     IF NEW.brandQC = '1' THEN   SET @brandQCDate = CURDATE();   ELSE   SET @brandQCDate = NULL;   END IF;       END

For some reason it does not update my Date field into the current date when QC is = 1. I've checked the mysql doc and it should work. Any ideeas?

Answer

If your column is named brandQCDate, then remove the @. The @ makes it a user defined variable, not the column. Also you want to make this a BEFORE UPDATE trigger.

CREATE TRIGGER `qc_date_trigger` BEFORE UPDATE ON `brand`  FOR EACH ROW BEGIN     IF NEW.brandQC = '1' THEN   SET NEW.brandQCDate = CURDATE();   ELSE   SET NEW.brandQCDate = NULL;   END IF;       END