mysql change value on insert/update

trigger.sql

-- example mysql triggers that change column values
-- before they are inserted/updated.

-- use case: when building code that comes from a git repository
-- we want to track the branch information, but some of the jobs
-- building the product use references rather than short names.
-- e.g. refs/remotes/origin/my/branch rather than origin/my/branch.

delimiter //

-- this is the table with the field we want to massage on insert/update
-- e.g. remove refs/remotes/ or origin/ prefix.

CREATE TABLE `builds` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `commit` char(40) NOT NULL,
  `branch` varchar(64) DEFAULT NULL,
  `created` datetime NOT NULL,
  KEY `branch_index` (`branch`) USING HASH
) ENGINE=InnoDB; //


-- use a function so that multiple triggers can share the same code

CREATE FUNCTION shorten_branch_name (data VARCHAR(64))
RETURNS VARCHAR(64) DETERMINISTIC
BEGIN
    DECLARE branch VARCHAR(64);
    SET branch = data;
    IF branch like 'refs/remotes/%' THEN
        SET branch = SUBSTRING(branch FROM 14);
    END IF;
    IF branch like 'origin/%' THEN
        SET branch = SUBSTRING(branch FROM 8);
    END IF;
    RETURN branch;
END; //


CREATE TRIGGER rename_branch_on_update BEFORE UPDATE ON builds
FOR EACH ROW
BEGIN
    SET NEW.branch = shorten_branch_name(NEW.branch);
END; //


CREATE TRIGGER rename_branch_on_insert BEFORE INSERT ON builds
FOR EACH ROW
BEGIN
    SET NEW.branch = shorten_branch_name(NEW.branch);
END; //


delimiter ;