MySQL trigger Calculate previous reading difference with current reading

Question

MySQL trigger Calculate previous reading difference with current reading

Navigation

#1 by (0 votes)

1

I have a table that is of meter readings, the fields are:

id_lecturas int(10) unsigned NOT NULL
fk_contadores int(10) unsigned NULL
fecha_lecturas datetime NULL
valor_lecturas decimal(10,3) NULL
diferencia_lecturas decimal(10,3) NULL

When a record is inserted or modified, all fields except the diferencia_lecturas are filled in.

The field difference_readings need to be calculated with respect to the previous reading and I think the best option is a trigger.

I have created one like this, but it gives an error:

TRIGGER 'calc_difference_update' AFTER UPDATE ON 'lecturas' 
FOR EACH ROW BEGIN

    IF NEW.valor_lecturas != OLD.valor_lecturas THEN
        UPDATE lecturas
        SET NEW.diferencia_lecturas = (NEW.valor_lecturas - IFNULL((
            SELECT L2.valor_lecturas 
            FROM lecturas AS L2 
            WHERE NEW.fk_contadores = L2.fk_contadores AND L2.fecha_lecturas < NEW.fecha_lecturas AND L2.id_lecturas != NEW.id_lecturas), 0))
        WHERE id_lecturas = NEW.id_lecturas;
    END IF;

END;

The error is: You can not specify target table lecturas for update in FROM clause

Can someone tell me what I'm doing wrong?

Thank you.

mysql trigger calculo-relacional

asked by Pau Vidal 05.11.2017 в 13:19

source

1 answer

pass data between activities [closed] Can someone tell me where I have the error?

score 0 · Accepted Answer

The error is giving you because in a UPDATE sentence you can not modify a field on a table if in the update statement itself you are accessing the same table. (This could generate problems of loops of updates on the table and to throw down your BD).

You should take a solution that goes by selecting the value you want from the readings table before you get to update it.

Something of the style:

-- Primero obtenemos el dato para actualizar la tabla
SELECT 
(NEW.valor_lecturas - IFNULL((
            SELECT L2.valor_lecturas 
            FROM lecturas AS L2 
            WHERE NEW.fk_contadores = L2.fk_contadores AND L2.fecha_lecturas < NEW.fecha_lecturas AND L2.id_lecturas != NEW.id_lecturas), 0)) into @varParaHacerUpdate FROM lectura
        WHERE id_lecturas = NEW.id_lecturas;

-- Y una vez tienes el valor para actualizar la sentencia update
UPDATE lecturas
        SET NEW.diferencia_lecturas = @varParaHacerUpdate
        WHERE id_lecturas = NEW.id_lecturas;