[SQL] 纯文本查看 复制代码 -- 创建触发器
DELIMITER //
CREATE TRIGGER employees_before_insert
BEFORE INSERT ON `员工表`
FOR EACH ROW
BEGIN
DECLARE current_year INT;
DECLARE last_job_number INT;
SET current_year = YEAR(CURRENT_TIMESTAMP);
-- 获取最后一条记录的年份
SELECT YEAR(created_at) INTO last_job_number
FROM `员工表`
ORDER BY id DESC
LIMIT 1;
-- 如果年份改变,重置编号为1
IF last_job_number < current_year THEN
SET NEW.job_number = CONCAT('DD', current_year, '00001');
ELSE
-- 否则,在最后一条记录的基础上自增编号
SET NEW.job_number = CONCAT('DD', current_year, LPAD(SUBSTRING_INDEX(last_job_number, current_year, -1) + 1, 5, '0'));
END IF;
END;
//
DELIMITER ;
|