sample.sql

CREATE TABLE `customer` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `npid` char(8) CHARACTER SET ascii NOT NULL,
  `email` char(254) CHARACTER SET ascii DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `npid` (`npid`),
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8;

DELIMITER ;;
CREATE trigger before_insert_customer BEFORE INSERT ON customer FOR EACH ROW 
    BEGIN  
        DECLARE tmp char(8);
        SET NEW.created=IF(ISNULL(NEW.created) OR NEW.created='0000-00-00 00:00:00', CURRENT_TIMESTAMP, IF(NEW.created<CURRENT_TIMESTAMP, NEW.created, CURRENT_TIMESTAMP));

        SET NEW.updated=NEW.created; 

        SET NEW.updated=NEW.created;                 
        REPEAT
            SET NEW.npid = SUBSTRING(sha1(concat(uuid(),'5MTYUqCv5MTYUqCv')), 1, 8);
            select npid into tmp from customer where npid = NEW.npid;
        UNTIL Row_Count() = 0 END REPEAT;
    END;;
DELIMITER ;