Trigger delete BEFORE INSERT

Question

I created the following trigger in Mysql:

DELIMITER //

CREATE TRIGGER delete_G8_dados_indicadores_antes_insert
BEFORE INSERT
   ON G8_dados_indicadores FOR EACH ROW

BEGIN

   delete from G8_dados_indicadores where id_indicador=NEW.id_indicador and ano = NEW.ano;

END; //

DELIMITER ;

The idea is that before entering a record in G8_dados_indicadores table, the system triggers the trigger automatically removing existing records. But to run an insert, mysql returns the following error:

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

What can it be?

Shadow · Accepted Answer · 2016-03-24 16:51:19Z

1

This is a known limitation in mysql, you have to find a workaround. In this particular instance, I would not even use a trigger, but either use replace into ... or insert ... on duplicate key update ... instead of an insert.

Replace into deletes the old row and inserts the new one, insert ... on duplicate key update ... does an update instead of an insert, if the record already exists.

answered Mar 24, 2016 at 16:51

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trigger delete BEFORE INSERT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related