如何创建触发器实现trans表ref_nbr按tx_type匹配关联表字段

现有trans表包含tx_type、ref_nbr两个字段，需实现两类强一致性约束：

• 当tx_type取值为D或W时，ref_nbr字段值必须能匹配branch表的branch_nbr字段值
• 当tx_type取值为B、P或R时，ref_nbr字段值必须能匹配mer表的mer_nbr字段值

触发器需要覆盖INSERT、UPDATE两类写操作，在数据正式写入表前完成校验，不满足规则时直接抛出异常阻断非法数据写入，以下以MySQL环境为例给出实现代码：

插入操作校验触发器 #

DELIMITER //
CREATE TRIGGER trg_trans_insert_check
BEFORE INSERT ON trans
FOR EACH ROW
BEGIN
    DECLARE branch_match_cnt INT DEFAULT 0;
    DECLARE mer_match_cnt INT DEFAULT 0;

    IF NEW.tx_type IN ('D','W') THEN
        SELECT COUNT(*) INTO branch_match_cnt
        FROM branch
        WHERE branch_nbr = NEW.ref_nbr;
        IF branch_match_cnt = 0 THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '数据校验失败：tx_type为D/W时，ref_nbr必须是已存在的branch_nbr';
        END IF;
    ELSEIF NEW.tx_type IN ('B','P','R') THEN
        SELECT COUNT(*) INTO mer_match_cnt
        FROM mer
        WHERE mer_nbr = NEW.ref_nbr;
        IF mer_match_cnt = 0 THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '数据校验失败：tx_type为B/P/R时，ref_nbr必须是已存在的mer_nbr';
        END IF;
    END IF;
END //
DELIMITER ;

更新操作校验触发器 #

更新操作只有在tx_type或ref_nbr字段发生变更时才需要触发校验，避免无意义的性能损耗：

DELIMITER //
CREATE TRIGGER trg_trans_update_check
BEFORE UPDATE ON trans
FOR EACH ROW
BEGIN
    DECLARE branch_match_cnt INT DEFAULT 0;
    DECLARE mer_match_cnt INT DEFAULT 0;

    IF NEW.tx_type <> OLD.tx_type OR NEW.ref_nbr <> OLD.ref_nbr THEN
        IF NEW.tx_type IN ('D','W') THEN
            SELECT COUNT(*) INTO branch_match_cnt
            FROM branch
            WHERE branch_nbr = NEW.ref_nbr;
            IF branch_match_cnt = 0 THEN
                SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = '数据校验失败：tx_type为D/W时，ref_nbr必须是已存在的branch_nbr';
            END IF;
        ELSEIF NEW.tx_type IN ('B','P','R') THEN
            SELECT COUNT(*) INTO mer_match_cnt
            FROM mer
            WHERE mer_nbr = NEW.ref_nbr;
            IF mer_match_cnt = 0 THEN
                SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = '数据校验失败：tx_type为B/P/R时，ref_nbr必须是已存在的mer_nbr';
            END IF;
        END IF;
    END IF;
END //
DELIMITER ;

落地注意事项 #

• 上述代码适配MySQL 5.5及以上版本，若使用PostgreSQL、SQL Server等其他数据库，只需调整异常抛出、触发器定义的语法格式，核心校验逻辑无需改动。
• 需提前为branch.branch_nbr、mer.mer_nbr创建主键或唯一约束，既保证关联值的唯一性，也能大幅提升校验查询的执行效率。
• 若业务中tx_type存在D/W/B/P/R之外的取值，可根据业务规则补充对应校验逻辑，也可增加非法tx_type的拦截规则。

内容的提问来源于stack exchange，提问作者nick shetty