I do not have much practice in MySQL and I need to create a trigger to update a column of a table when a new record is inserted, but when you insert a new row, nothing happens. I've already changed everything I could and I could not make the trigger work. Where is my error?
delimiter $$
create trigger Database.Atualiza_Compra
before insert on Database.CompraPagamento for each row
Begin
if new.metodo = 'metodo_compra'
then
set new.tipo = concat(substring(new.numero_adicional, 31, 1),'-', new.tipo);
-- set new.tipo = '3-Tipo';
end if;
END$$
Note: When I make a purchase, other tables also receive similar records, but I just need this specific table to have the column in that format for my API to work. I do not know if that has anything to do with it.
I also tried to do as it is mentioned in the trigger. If the method is one, "set" a direct value and also did not work.
Can you have a problem with double quotes, simple quotes or crass?
When I do the insert and the trigger does not work, I then run an Update command to update the new line passing the same parameters and it works.
update Database.Atualiza_Compra
set tipo = concat(substring(numero_adicional, 31, 1),'-', tipo)
where metodo = 'metodo_compra' and id = 1