Can't update table 'blad' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

Question

I have table:

    CREATE TABLE blad
(
id INT NOT NULL UNIQUE AUTO_INCREMENT, 
komunikatBledu TINYTEXT,
czujnik TINYTEXT,
aktualny BOOLEAN DEFAULT TRUE,
zakoncz BOOLEAN DEFAULT FALSE,
czas DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY(id)
);

Sample record:

INSERT INTO blad (komunikatBledu, czujnik, aktualny, zakoncz) VALUES ('No RTC', 'czujnikPiec', true, false)

I want the record insert that has zakoncz- true to change all records on fasle if it was true.

I wrote trigger:

BEGIN
IF (((SELECT COUNT(*) FROM blad WHERE komunikatBledu = NEW.komunikatBledu AND czujnik = NEW.czujnik AND aktualny = true) > 1) AND (NEW.zakoncz = true)) THEN
UPDATE blad
SET aktualny = false
WHERE id IN (SELECT Id FROM (SELECT id FROM blad WHERE komunikatBledu = NEW.komunikatBledu AND czujnik = NEW.czujnik AND aktualny = true AND id != (SELECT MAX(id) FROM blad)) as t);
END IF;
END

When I do insert:

INSERT INTO blad (komunikatBledu, czujnik, aktualny, zakoncz) VALUES ('No RTC', 'czujnikPiec', false, true)

I get the error:

Can't update table 'blad' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Please help

Answer 1

The error message can be believed. You can't insert/update/delete the same table for which the trigger executed, because it might result in an infinite loop.

In your case, you can't do what you want with a trigger. You must do this with application code. Here's pseudocode:

$aktualny = false;
$komunikatBledu = 'No RTC';
$czujnik = 'czujnikPiec';

IF $aktualny == true THEN
  UPDATE blad SET aktualny = false 
  WHERE komunikatBledu = $komunikatBledu
  AND czujnik = $czujnik;
END

INSERT INTO blad (komunikatBledu, czujnik, aktualny, zakoncz) 
VALUES ($komunikatBledu, $czujnik, $aktualny, true);

This code isn't literal code. It's just meant to describe the logic you need it to do. I'll leave it to you to adapt it into your own client programming language.