¿because I can not update a table associated with a triggers on mysql?

Question

I am programming a trigger (in workbench 8) for mysql that is triggered after an insert to the table, my trigger is as follows:

 DELIMITER //
Drop trigger if exists tr_pago;
CREATE TRIGGER tr_pago after insert on pago for each row 
BEGIN
    declare ultimoidpago int;
    declare idcompromisopago int;
    declare idunidadrecaudadora int;
    declare idboleta int;

    Set ultimoidpago = new.IdPago
    Set idcompromisopago  = (select new.CP_IdCompromisoPago from pago where IdPago = ultimoidpago);
    Set idunidadrecaudadora = (select UR_IdUnidadRecaudadora from compromiso_pago where IdCompromisoPago = idcompromisopago);
    Set idboleta = (select IdBoleta from boleta where UR_IdUnidadRecaudadora = idunidadrecaudadora );
    update pago set new.B_IdBoleta = idboleta where IdPago = ultimoidpago;
END
DELIMITER //

But when making an insert to the payment table, I get the following error:

Error Code: 1442. Can't update table 'pago' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Possible duplicate of [MySQL - Trigger for updating same table after insert](https://stackoverflow.com/questions/12877732/mysql-trigger-for-updating-same-table-after-insert) — Madhur Bhaiya, Oct 12 '18 at 18:45

spencer7593 · Answer 1 · 2018-10-12T19:24:38.807

This is a MySQL restriction in a trigger. Within a trigger, it is not allowed to issue a DML statement (INSERT/UPDATE/DELETE) against any table that is referenced in the statement that caused the trigger to be fired.

Looks like we are wanting to set a column in the inserted row to a value.

This would typically be better handled in a BEFORE INSERT trigger. A BEFORE INSERT trigger is allowed to perform SELECT on other tables.

SET NEW.B_IdBoleta = expr ;

FOLLOWUP

It's not clear what we are intending this trigger to do. But we know it can't issue an UPDATE on pago.

Here's an example of a BEFORE INSERT trigger that attempts to set the value of the b_idboleta column from the result of a query. We add a continue handler to catch the error if the SELECT query doesn't return a row.

 DELIMITER //

 CREATE TRIGGER tr_pago
 BEFORE INSERT ON pago
 FOR EACH ROW
 BEGIN
    DECLARE CONTINUE HANDLER FOR 1329 BEGIN SET NEW.b_idboleta = NULL; END;
    SELECT bo.idboleta
      INTO NEW.b_idboleta
      FROM compromiso_pago cp
      JOIN boleta bo
        ON bo.ur_idunidadrecaudadora = cp.ur_idunidadrecaudadora
     WHERE cp.idcompromisopago = NEW.cp_idcompromisopago
     ORDER
        BY bo.idboleta
     LIMIT 1;
 END//

 DELIMITER ;

try to perform the update with before and I have the same problem — Pablo Moraga, Oct 12 '18 at 18:57
@PabloMoraga: Do NOT perform an UPDATE. Just SET the value of the column being inserted, using the special `NEW.` qualifier. — spencer7593, Oct 12 '18 at 18:58

¿because I can not update a table associated with a triggers on mysql?

1 Answers1