MariaDB中用触发器和存储过程删除旧值的实现问题

表结构与初始数据 #

表code_detail包含复合主键id和code，字段value，结构如下：

CREATE TABLE `code_detail` (
  `id` int(11) NOT NULL,
  `code` varchar(6) NOT NULL,
  `value` float DEFAULT NULL,
  PRIMARY KEY (`id`,`code`),
  CONSTRAINT `code_detail_ibfk_1` FOREIGN KEY (`id`) REFERENCES `code_assignment` (`id`),
  CONSTRAINT `code_detail_ibfk_2` FOREIGN KEY (`code`) REFERENCES `code` (`code`)
)

初始数据：

需求描述 #

需要替换id=2的所有记录：删除该id下的旧记录（C、D），同时插入新的记录（E、F），最终结果如下：

当前尝试的问题 #

使用INSERT ... ON DUPLICATE KEY UPDATE语句：

INSERT INTO code_detail 
(id, code, value)
VALUES ((2, 'E', 50), (2, 'F', 50))
ON DUPLICATE KEY UPDATE
value = VALUES(value)

该语句仅能更新主键已存在的记录，但无法删除不在新插入集合中的旧记录（C、D），不符合需求。

触发器方案的错误 #

尝试创建触发器删除旧记录时，报错Error Code: 1363. There is no OLD row in on INSERT trigger，触发器代码如下：

DELIMITER $$
CREATE TRIGGER after_code_update 
AFTER INSERT ON code_detail
FOR EACH ROW
BEGIN
     IF old.id = new.id AND old.code NOT IN (VALUES(new.code)) THEN
         CALL STORED_PROCEDURE(old.id, old.code);
     END IF;
END$$
DELIMITER ;

错误原因：

• AFTER INSERT触发器仅针对新插入的行，不存在OLD行（OLD仅在UPDATE/DELETE触发器中有效）。
• 触发器是逐行触发，只会处理当前插入的行，不会遍历表中所有旧行，因此无法识别不在新集合中的旧code。

解决方案 #

方案1：手动事务（简单直接） #

先删除指定id的所有旧记录，再插入新记录，用事务保证原子性：

START TRANSACTION;
-- 删除id=2的所有旧记录
DELETE FROM code_detail WHERE id = 2;
-- 插入新记录
INSERT INTO code_detail (id, code, value) VALUES (2, 'E', 40), (2, 'F', 60);
COMMIT;

如果需要兼容“部分更新（保留新集合中存在的旧记录并更新value）”，可以先插入/更新新记录，再删除不在新集合中的旧记录：

START TRANSACTION;
-- 插入或更新新记录
INSERT INTO code_detail (id, code, value) VALUES (2, 'E', 40), (2, 'F', 60)
ON DUPLICATE KEY UPDATE value = VALUES(value);
-- 删除id=2下不在新集合中的旧记录
DELETE FROM code_detail 
WHERE id = 2 
AND code NOT IN ('E', 'F');
COMMIT;

方案2：封装存储过程（复用性高） #

创建存储过程封装整个逻辑，支持传入id和新记录的code、value：

DELIMITER $$
CREATE PROCEDURE replace_code_detail(
    IN target_id INT,
    -- 如需动态传递多条记录，建议改用临时表，此处示例两条固定记录
    IN new_code1 VARCHAR(6), IN new_value1 FLOAT,
    IN new_code2 VARCHAR(6), IN new_value2 FLOAT
)
BEGIN
    START TRANSACTION;
    -- 删除目标id下的所有旧记录
    DELETE FROM code_detail WHERE id = target_id;
    -- 插入新记录
    INSERT INTO code_detail (id, code, value)
    VALUES (target_id, new_code1, new_value1), (target_id, new_code2, new_value2);
    COMMIT;
END$$
DELIMITER ;

调用示例：

CALL replace_code_detail(2, 'E', 40, 'F', 60);

额外疑问解答 #

触发器只会处理触发操作涉及的行：比如AFTER INSERT触发器只会遍历本次插入的每一行，不会扫描整个表的所有旧行。因此你之前的触发器方案无法覆盖旧记录，确实不可行。

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