MYSQL blocks the SELECT when executing an UPDATE in a trigger

Question

MYSQL blocks the SELECT when executing an UPDATE in a trigger

Navigation

#1 by (2 votes)

3

This is the error I receive

SQLSTATE [HY000]: General error: 1442 Can not update table 'accounts_cobrate' in stored function / trigger because it is used by statement which invoked this stored function / trigger.

If I use a static id instead of the query to get the ID of the account to be charged within INSERT , everything works normally.

Here are the 2 procedures:

TRIGGER tr_cuentascobrar_nota (In this trigger, the problem occurs):

CREATE TRIGGER tr_cuentascobrar_nota AFTER UPDATE
ON nc_factura FOR EACH ROW
BEGIN
    IF ( NEW.estadoenvio = 1 ) THEN
            INSERT INTO detalle_cuentas_cobrar ( cuenta_cobrar, monto_cancelado,fecha_actualizacion, banco )
            VALUES ( (SELECT (idcuentas_cobrar) FROM cuentas_cobrar WHERE comprobante = NEW.comprobante_id), NEW.Total, NOW(), 20 );
    END IF ;
END;

TRIGGER updateCount Account:

CREATE TRIGGER actualizarCuentaCobrar BEFORE INSERT
ON detalle_cuentas_cobrar FOR EACH ROW
BEGIN

    UPDATE cuentas_cobrar SET monto_rest  = monto_rest - NEW.monto_cancelado
    WHERE idcuentas_cobrar = NEW.cuenta_cobrar;

    IF ( (SELECT(monto_rest) FROM cuentas_cobrar WHERE idcuentas_cobrar = NEW.cuenta_cobrar )  = 0) THEN
        UPDATE cuentas_cobrar SET estado = 'PAGADO' WHERE idcuentas_cobrar = NEW.cuenta_cobrar;
    END IF ;
END

mysql sql trigger

asked by w33haa 22.03.2017 в 15:19

source

1 answer

Go through an ArrayList and show one by one How can I hide a query string from the url?

score 2 · Accepted Answer

To begin with, I just want to mention that although I do not understand everything you are trying to do with the triggers, it is possible that you are trying to do too much with the triggers. It would be good to evaluate if there are better options for what you do.

With that said, the precise error you receive is due to the following sentence:

INSERT INTO detalle_cuentas_cobrar ( cuenta_cobrar, monto_cancelado,fecha_actualizacion, banco )
VALUES ( (SELECT (idcuentas_cobrar) FROM cuentas_cobrar WHERE comprobante = NEW.comprobante_id), NEW.Total, NOW(), 20 );

The INSERT statement includes a query to the cuentas_cobrar table, but at the same time, the INSERT triggers the trigger actualizarCuentaCobrar that tries to modify that same table. This double simultaneous action with the same table is not allowed.

The solution is to divide the statement INSERT into 2:

First you query the table cuentas_cobrar using a SELECT ... INTO to keep the result.

Then you use the result to execute INSERT .

Code (note that in the DECLARE I used the type INT to choose one, set it to be the type that corresponds to the column idcuentas_cobrar ):

CREATE TRIGGER tr_cuentascobrar_nota AFTER UPDATE
ON nc_factura FOR EACH ROW
BEGIN
    DECLARE _idcuentas_cobrar INT;

    IF NEW.estadoenvio = 1 THEN
            SELECT idcuentas_cobrar INTO _idcuentas_cobrar
            FROM cuentas_cobrar WHERE comprobante = NEW.comprobante_id;

            INSERT INTO detalle_cuentas_cobrar (cuenta_cobrar, monto_cancelado,fecha_actualizacion, banco)
            VALUES (_idcuentas_cobrar, NEW.Total, NOW(), 20);
    END IF;
END;