1
I don’t have much practice in Mysql and 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 changed everything I could and I couldn’t make Rigger work. Where’s my mistake?
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 stay with the column in this format for my API to work. I don’t know if this has anything to do.
I also tried to do as it is commented on on Rigger. If the method is that, "set" a direct value and also did not work.
You may have a problem with double quotes, single quotes or crase?
When I do Insert and Trigger doesn’t work, I then run an Update command to update the new line by 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
You can post the layout of your table?
– FabianoLothor
If you can also post the
INSERTthat you’re trying to do. Looking at it this way, I imagine the problem may be in theIF.– Roberto de Campos
@Robertofagundes, the Insert is by the system and as I said reaches other tables. If I do an Update on that same table by passing exactly the concatenation exposed there, it works. It just doesn’t work on Trigger. I think it’s something in the if same but what? Syntax?
– VictorP
I imagine it’s the contents of the field
metodo, the value of it really ismetodo_compra?– Roberto de Campos
@Robertofagundes yes, that’s right. I pass this same content on Where to make an update script and he understands normally.
– VictorP
Put in your question to
querywhich is being executed, by what you’re telling me, you’re running aUPDATEand expecting aTRIGGERBEFORE INSERTbe executed. If so, it will not be executed...– Roberto de Campos
@Victorp puts the
INSERTthat is being done, for thatTRIGGERwork it is necessary to pass the fieldmetodowith the valuemetodo_comprain theINSERT.– Roberto de Campos
@Robertofagundes Insert is not a command, it’s the application. The customer makes a purchase. When he chooses a particular type of purchase the method field I need it to be a type concatenation plus additional information. When I make a test purchase, I look at the bank and Trigger didn’t work, so I run that update script to leave the field the way I need it. Insert is not a manual script and the application affects other tables. But I just need this table and this specific field to be changed.
– VictorP
Your application somehow mounts a
querywhich is the commandINSERT, if you don’t have access to make the application also put the fieldmetodo=metodo_comprain this commandINSERT, that oneTRIGGERwill never work.– Roberto de Campos
I don’t have access to the application code, just the database. But if every time I make a purchase of a type and always in the column is 'methodo_purchase' does not mean that the application is putting the 'purchase method' ? And there would be another way?
– VictorP