在业务系统中，标签状态往往和业务数据的变化强关联，比如订单标签会根据订单的支付、发货、退款等操作自动切换状态。如果通过业务代码手动更新标签状态，不仅会增加代码复杂度，还可能出现状态更新遗漏的情况。利用SQL触发器结合状态机逻辑，可以在数据库层面自动完成标签状态的更新，保证状态流转的准确性。
状态机规则设计
首先需要明确标签的所有可能状态以及状态之间的流转规则，假设我们有一个商品标签表，标签状态包含待审核、已上架、已下架、已删除四个状态，流转规则如下：
- 待审核状态只能流转到已上架或者已删除
- 已上架状态只能流转到已下架或者已删除
- 已下架状态只能流转到已上架或者已删除
- 已删除状态为终态，不可再流转
对应的标签表结构如下：
-- 商品标签表
CREATE TABLE product_tag (
id INT PRIMARY KEY AUTO_INCREMENT,
tag_name VARCHAR(50) NOT NULL COMMENT '标签名称',
status VARCHAR(20) NOT NULL DEFAULT '待审核' COMMENT '标签状态：待审核、已上架、已下架、已删除',
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '状态更新时间',
CHECK (status IN ('待审核', '已上架', '已下架', '已删除'))
);
触发器实现状态自动更新
我们可以创建一个UPDATE触发器，在标签表的status字段被修改时，自动校验状态流转是否符合规则，同时更新状态更新时间。如果流转不符合规则，触发器会抛出异常阻止操作。
MySQL环境触发器示例
DELIMITER //
CREATE TRIGGER update_tag_status_trigger
BEFORE UPDATE ON product_tag
FOR EACH ROW
BEGIN
-- 如果状态没有变化，直接返回
IF OLD.status = NEW.status THEN
SET NEW.update_time = CURRENT_TIMESTAMP;
ELSE
-- 校验状态流转规则
IF OLD.status = '待审核' THEN
IF NEW.status NOT IN ('已上架', '已删除') THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '待审核状态只能流转到已上架或已删除';
END IF;
ELSEIF OLD.status = '已上架' THEN
IF NEW.status NOT IN ('已下架', '已删除') THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '已上架状态只能流转到已下架或已删除';
END IF;
ELSEIF OLD.status = '已下架' THEN
IF NEW.status NOT IN ('已上架', '已删除') THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '已下架状态只能流转到已上架或已删除';
END IF;
ELSEIF OLD.status = '已删除' THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '已删除状态为终态，不可再流转';
END IF;
-- 更新状态更新时间
SET NEW.update_time = CURRENT_TIMESTAMP;
END IF;
END //
DELIMITER ;
PostgreSQL环境触发器示例
PostgreSQL的触发器需要先创建触发器函数，再绑定到表上：
-- 创建触发器函数
CREATE OR REPLACE FUNCTION check_tag_status_transition()
RETURNS TRIGGER AS $$
BEGIN
-- 如果状态没有变化，直接返回新行
IF OLD.status = NEW.status THEN
NEW.update_time = CURRENT_TIMESTAMP;
RETURN NEW;
END IF;
-- 校验状态流转规则
IF OLD.status = '待审核' THEN
IF NEW.status NOT IN ('已上架', '已删除') THEN
RAISE EXCEPTION '待审核状态只能流转到已上架或已删除';
END IF;
ELSEIF OLD.status = '已上架' THEN
IF NEW.status NOT IN ('已下架', '已删除') THEN
RAISE EXCEPTION '已上架状态只能流转到已下架或已删除';
END IF;
ELSEIF OLD.status = '已下架' THEN
IF NEW.status NOT IN ('已上架', '已删除') THEN
RAISE EXCEPTION '已下架状态只能流转到已上架或已删除';
END IF;
ELSEIF OLD.status = '已删除' THEN
RAISE EXCEPTION '已删除状态为终态，不可再流转';
END IF;
-- 更新状态更新时间
NEW.update_time = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- 绑定触发器到表
CREATE TRIGGER update_tag_status_trigger
BEFORE UPDATE ON product_tag
FOR EACH ROW
EXECUTE FUNCTION check_tag_status_transition();
触发器测试验证
我们可以通过以下测试语句验证触发器的逻辑是否正确：
-- 插入一条待审核的标签数据
INSERT INTO product_tag (tag_name, status) VALUES ('新品标签', '待审核');
-- 测试合法流转：待审核流转到已上架
UPDATE product_tag SET status = '已上架' WHERE id = 1;
-- 测试非法流转：已上架直接流转到待审核，会触发异常
UPDATE product_tag SET status = '待审核' WHERE id = 1;
使用注意事项
- 触发器会增加数据库的操作开销，如果业务并发量很高，需要评估触发器的性能影响，避免拖慢整体业务操作。
- 不同数据库厂商的触发器语法存在差异，编写时需要适配对应数据库的语法规则。
- 触发器的逻辑尽量保持简单，不要编写过于复杂的业务逻辑，否则会增加后续维护的难度。
- 如果需要修改状态流转规则，记得同步更新触发器的逻辑，避免出现规则不一致的问题。