Problem with trigger when updating the same table

Navigation
1

I can not get through the MySQL Database to control that everything is inserted in upper case, or update after inserting.

I have the following trigger in the table paises ( idPasi , pais ): 

CREATE DEFINER = CURRENT_USER TRIGGER 'tf-modelo-de-datos'.'paises_AFTER_INSERT' AFTER INSERT ON 'paises' FOR EACH ROW
BEGIN
    UPDATE tf.paises SET pais = UPPER(pais);
END

But when I insert a new country, I get the following error:

  

Operation failed: There was an error while applying the SQL script to   the database Executing: INSERT INTO tf . paises ( pais ) VALUES   ('Argentina');

     

ERROR 1442: 1442: Can not update table 'countries' in stored   function / trigger because it is already used by statement which invoked   this stored function / trigger. SQL Statement: INSERT INTO tf . paises   ( pais ) VALUES ('Argentina')

I read something that was trying to modify the same data twice at the same time.

Does anyone know any way to do this control in the most efficient way possible, but at the Database level?.

    
asked by GADGustavo67 11.09.2018 в 21:16
source

2 answers

5

You can not in any way update the same table affected by the trigger, because this would generate an infinite recursion. But what you can do is, modify the "pseudo" table NEW to adjust the value of the column in question, just that you must do it before the INSERT , that is to say to hang yourself from the BEFORE INSERT .

Example: 

DROP TABLE IF EXISTS Paises;

CREATE TABLE Paises (
  Nombre varchar(255)
);

CREATE TRIGGER trg_Paises BEFORE INSERT ON Paises
FOR EACH ROW BEGIN
    SET NEW.Nombre = UPPER(NEW.Nombre);
END
;

INSERT INTO Paises (Nombre) VALUE('argentina');

Here the Fiddle

    
answered by 11.09.2018 / 21:48
source
1

You have to use BEFORE INSERT and use the object NEW (for the record you are updating) and the object OLD (the previous record). 

CREATE TRIGGER lcase_insert BEFORE INSERT ON my_table FOR EACH ROW 
SET NEW.pais = UPPER(NEW.pais );

I hope to help.

    
answered by 11.09.2018 в 21:52
Problems showing some special characters [closed] removeClass JQUERY does not work