Update a field in the same table with a trigger

Question

Update a field in the same table with a trigger

Navigation

#1 by (3 votes)
#2 by (1 votes)

1

I would like to be able to update a field in the same table where the Trigger is triggered, I have read that when the trigger is triggered the table is blocked and can not be modified but it can be done in another way.

CREATE TRIGGER 'codigo_barras_update' AFTER UPDATE ON 'persona'
FOR EACH ROW
BEGIN
   DECLARE codigoEAN VARCHAR(13);
   Select CONCAT('1000',LEFT(NEW.nif, 8), '0') INTO codigoEAN;
   UPDATE  persona SET codigo_barras =  codigoEAN  WHERE nif = NEW.nif; 
END;

What I want to do is update the barcode with the NIF of the person, then as long as the NIF is changed, the trigger skips and generates the new bar code and stores it in the table person. The drawback is that the NIF and the bar code is in the same table. Would there be a possibility of doing it or being able to do it in another way?

mysql sql trigger

asked by Arkhan6 14.02.2017 в 13:57

source

2 answers

1

An alternative would be to use a Stored Procedure instead of a Trigger. The UPDATE would be done through this procedure and in the same procedure generate the script you want.

To this procedure you pass the new NIF and before doing the UPDATE you modify it as you are doing in the Trigger.

This link can be useful for you.

answered by 14.02.2017 в 14:06

How can I launch an intent by clicking on the child element of an expandable List? help with using CONCAT in php

score 3 · Accepted Answer

Instead of using a AFTER UPDATE trigger, you can use a BEFORE UPDATE trigger. In that case, you have the opportunity to assign the value to an field before that completes the UPDATE statement. You do not have to worry about any blocking of the table because you do not need to perform a UPDATE within the trigger. You only need to assign the value to the field directly.

Here is an example of using a BEFORE UPDATE trigger for your case:

DELIMITER //
CREATE TRIGGER 'codigo_barras_update' BEFORE UPDATE ON 'persona'
FOR EACH ROW 
BEGIN
  SET NEW.codigo_barras = CONCAT('1000',LEFT(NEW.nif, 8), '0');
END;//
DELIMITER ;