MySQL触发器更新almacen表异常及1175错误求助

问题现象 #

在registro_producto表插入数据后，触发器更新almacen表时出现数据重复/新增产品数据重复问题，同时触发以下错误：

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

表结构代码 #

create table registro_producto(
id_producto int(5) not null,
id_proveedor int(5) not null,
    id_trabajador int(5) not null,
    nombre_producto varchar(20) not null,
    u_medida varchar(20) not null,
    cantidad_producto decimal(8,3) not null,
    clase_producto varchar(20) not null,
constraint PK_ID_Producto primary key(id_producto),
constraint FK_Proveedor1 foreign key (id_proveedor) references proveedor(id_proveedor),
    constraint FK_ID_Trabajador1 foreign key(id_trabajador) references trabajador(id_trabajador),
    constraint CK_Clase_Producto check (clase_Producto in ('perecible','no perecible'))
);

create table almacen(
    id_almacen int(5) not null,
        id_producto int(5) not null,
    fecha_entrada date not null,
    cantidad_total decimal(8,3) not null,
    cantidad_restante decimal(8,3),
    constraint pk_id_almacen primary key (id_almacen),
    constraint fk_id_producto foreign key (id_producto) references registro_producto(id_producto)
);

原触发器代码 #

DELIMITER //
CREATE TRIGGER tr_actualizar_almacen_registro_producto
AFTER INSERT ON registro_producto
FOR EACH ROW
BEGIN
    -- Actualizar cantidad total y restante del producto
    UPDATE almacen
    SET cantidad_total = cantidad_total + NEW.cantidad_producto,
        cantidad_restante = cantidad_restante + NEW.cantidad_producto
    WHERE id_producto = NEW.id_producto;
    
    -- Actualizar cantidad restante del producto en base a las entradas anteriores
    UPDATE almacen a
    INNER JOIN (
        SELECT id_producto, SUM(cantidad_producto) AS cantidad_total
        FROM registro_producto
        GROUP BY id_producto
    ) r ON a.id_producto = r.id_producto
    SET a.cantidad_restante = r.cantidad_total - a.cantidad_total;
END//
DELIMITER ;

问题分析与解决方案 #

1. 1175错误原因与修复 #

原触发器中第二个UPDATE语句没有指定针对当前插入产品的过滤条件，会尝试更新整个almacen表，触发MySQL安全更新模式的限制。同时该语句的逻辑完全错误，会导致cantidad_restante计算混乱，直接删除即可。

2. 数据重复问题修复 #

原触发器未处理产品首次入库的场景：当almacen中无对应产品记录时，第一个UPDATE不会执行，后续也没有插入逻辑，若重复插入同产品会导致数据异常。需增加判断，无记录时插入新条目。

修改后的触发器代码 #

DELIMITER //
CREATE TRIGGER tr_actualizar_almacen_registro_producto
AFTER INSERT ON registro_producto
FOR EACH ROW
BEGIN
    -- 累加已有产品的库存总量和剩余量
    UPDATE almacen
    SET cantidad_total = cantidad_total + NEW.cantidad_producto,
        cantidad_restante = cantidad_restante + NEW.cantidad_producto
    WHERE id_producto = NEW.id_producto;
    
    -- 若未找到对应产品记录（首次入库），插入新库存条目
    IF ROW_COUNT() = 0 THEN
        INSERT INTO almacen(id_producto, fecha_entrada, cantidad_total, cantidad_restante)
        VALUES(NEW.id_producto, CURDATE(), NEW.cantidad_producto, NEW.cantidad_producto);
    END IF;
END//
DELIMITER ;

临时关闭安全更新模式（可选） #

若仍触发1175错误，可在执行触发器前临时关闭安全模式：

SET SQL_SAFE_UPDATES = 0;

操作完成后建议重新开启：

SET SQL_SAFE_UPDATES = 1;

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