How do I create this MySQL trigger?

Question

I have a table called customer which has, among others, a column called name and a column called first_name_start. I want first_name_start to be equal to SUBSTRING(name, 1, 4). How would I create a trigger that makes sure this happens?

Answer 1

DELIMITER $$

CREATE TRIGGER trigger_name BEFORE INSERT ON your_table
FOR EACH ROW 
BEGIN
  SET NEW.first_name_start = SUBSTRING(NEW.name, 1, 4);  
END$$

CREATE TRIGGER trigger_name BEFORE UPDATE ON your_table
FOR EACH ROW 
BEGIN  
  SET NEW.first_name_start = SUBSTRING(NEW.name, 1, 4);
END$$

DELIMITER ;

Unfortunately, I didn't get time to test this, it might be completely wrong.

Answer 2

Why not just make a view which adds the first_name_start if you really need it as a column? Then you won't be storing redundant data, but you will be able to use it as if you were.