问题描述:

使用spring boot的时,候访问更新数据库内容接口报错:

Error updating database. Cause: java.sql.SQLException: Can't update table 'category' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

问题分析:

数据库更新的字段使用了更新数据库内容的触发器,而这是不被允许的

因为MySQL的限制,MySQL不允许在触发器中直接更新触发该触发器的表，也就是说在触发器中不能更新category表。

尝试解决:

为了解决这个问题，可以考虑将触发器的逻辑实现在代码层,并且删除MySQL中的触发器。这样可以绕过MySQL触发器的限制。

比如你定义了一个改一条就可以实现将所有相关的数据的值都进行更改的触发器

DELIMITER //
CREATE TRIGGER update_child_is_agriculture
BEFORE UPDATE ON category
FOR EACH ROW
BEGIN
    -- 判断是否更新了is_agriculture字段
    IF NEW.is_agriculture <> OLD.is_agriculture THEN
        -- 更新子数据的is_agriculture值
        UPDATE category
        SET is_agriculture = NEW.is_agriculture
        WHERE parent_id = OLD.cat_id;
    END IF;
END;
//
DELIMITER ;

这时,你访问端口时可能会报错:

 Error updating database.  Cause: java.sql.SQLException: Can't update table 'category' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

先删除触发器:

DROP TRIGGER IF EXISTS trg_category_update_children_is_agriculture;

再将功能逻辑定义到你的代码中实现,再在接口调用逻辑方法就可以实现功能了

代码示例

// 更新category表的is_agriculture字段
String updateCategorySql = "UPDATE category SET is_agriculture = ? WHERE cat_id = ?";
try {
    // 开始事务
    dataSourceTransactionManager.beginTransaction();

    // 更新category表的is_agriculture字段
    jdbcTemplate.update(updateCategorySql, newIsAgricultureValue, categoryId);

    // 更新子数据的is_agriculture字段
    for (Category child : childrenToUpdate) {
        jdbcTemplate.update(updateCategorySql, newIsAgricultureValue, child.getCatId());
    }

    // 提交事务
    dataSourceTransactionManager.commitTransaction();
} catch (Exception e) {
    // 回滚事务
    dataSourceTransactionManager.rollbackTransaction();
    // 处理异常
    e.printStackTrace();
}