同MariaDB服务器跨数据库用户表UPSERT存储过程报错求助

错误分析 #

你写的存储过程存在几个关键问题：

• 直接引用tbl_members_contacts的字段无效：存储过程未关联该表，也未接收触发器传递的行数据，无法直接访问表字段。
• ON DUPLICATE KEY UPDATE语法错误：VALUES()函数内应填写目标表的列名，而非源表字段；且目标表字段无需重复写表名前缀。
• 缺少参数传递：触发器调用存储过程时，需要将触发行的字段值传递给存储过程。

修正后的存储过程 #

在members数据库中创建如下存储过程：

DELIMITER $$

CREATE PROCEDURE sec_usersUpdate(IN p_MemberID INT, IN p_ContactTypeID INT, IN p_ContactInfo VARCHAR(255))
BEGIN
    IF p_ContactTypeID = 1 THEN
        INSERT INTO members_sec.sec_users (login, email)
        VALUES (p_MemberID, p_ContactInfo)
        ON DUPLICATE KEY UPDATE
            email = VALUES(email); -- login是主键，重复时无需更新，仅更新email即可
    END IF;
END$$

DELIMITER ;

配套触发器创建 #

因为你计划通过触发器调用存储过程，需要在tbl_members_contacts表上创建INSERT和UPDATE触发器：

INSERT触发器 #

DELIMITER $$

CREATE TRIGGER trg_members_contacts_insert
AFTER INSERT ON tbl_members_contacts
FOR EACH ROW
BEGIN
    CALL sec_usersUpdate(NEW.MemberID, NEW.ContactTypeID, NEW.ContactInfo);
END$$

DELIMITER ;

UPDATE触发器 #

DELIMITER $$

CREATE TRIGGER trg_members_contacts_update
AFTER UPDATE ON tbl_members_contacts
FOR EACH ROW
BEGIN
    CALL sec_usersUpdate(NEW.MemberID, NEW.ContactTypeID, NEW.ContactInfo);
END$$

DELIMITER ;

关键说明 #

• 存储过程添加了三个输入参数，用于接收触发器传递的当前行数据。
• ON DUPLICATE KEY UPDATE中，login是目标表的主键，重复时主键值不会变化，因此仅需更新email字段。
• 触发器使用NEW关键字获取插入/更新后的行数据，传递给存储过程处理。

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