mysql-trigger-example.sql

DROP TRIGGER IF EXISTS  test.test_before_insert_trigger ;
CREATE TRIGGER test_before_insert_trigger BEFORE INSERT ON test
FOR EACH ROW 
SET NEW.id = ((substr((NOW()),1,4)*10000)+(substr((NOW()),6,2)*100)+(substr((NOW()),9,2)));




DROP TRIGGER IF EXISTS  `test`.`test_before_insert_trigger` ;
DELIMITER $$
CREATE TRIGGER `test_before_insert_trigger` BEFORE INSERT ON `test`
 FOR EACH ROW BEGIN 
	DECLARE max_id decimal(11);
	DECLARE new_id_prefix decimal(11);
	SET new_id_prefix = ((substr((NOW()),1,4)*10000)+(substr((NOW()),6,2)*100)+(substr((NOW()),9,2)));
	SET max_id = (SELECT MAX(id) FROM test WHERE substr(id,1,8) = substr(new_id_prefix,1,8));
	if max_id IS NOT NULL THEN
		SET NEW.id = max_id + 1;
    else	    
		SET NEW.id = new_id_prefix*1000+1;
	end if;
END$$
DELIMITER ;