mysql使用触发器实现自增

DELIMITER $$
CREATE TRIGGER prog_no_trigger
BEFORE INSERT ON prog_info
FOR EACH ROW 
	BEGIN 
		declare newpg_no varchar(16);
		declare pg_no varchar(16);
		declare pg_type varchar(4);
		declare pg_number int;
		set pg_type = NEW.prog_type;
		IF NEW.prog_no IS NULL OR NEW.prog_no = '' THEN
			SELECT MAX(prog_no) into pg_no from prog_info where prog_type = NEW.prog_type;
			if pg_no is null or pg_no = '' then 
				set newpg_no = CONCAT(pg_type, '000001');
			else
				set pg_number = RIGHT(pg_no,6) + 1;
				set newpg_no = RIGHT(CONCAT('000000',pg_number),6);
				SET newpg_no=CONCAT(pg_type,newpg_no);
			end if;
			SET NEW.prog_no = newpg_no;
		END IF;
	END