Trigger Syntax mySQL

Question

I want to make the Total column receive the total sum between the other columns, but i keeping getting the error 1193. I'm new to mySql so i dont know where i should start looking to solve the error.

DELIMITER $$
CREATE TRIGGER Total
BEFORE INSERT ON `despesas` FOR EACH ROW
BEGIN
    SET Total = Abertura_Processo+Telefone+Correspondencia+Cartorio+Conservatorio+Servico_Financas+Taxas_Emolumentos+Honorarios;
END;
$$
DELIMITER ;

I've read a bit and for what i've understood I need to create a variable, but i dont know how. The total column has its values manually inserted i want it to be automatically

I want to have the total value inserted into the database on the column total, so i can search for it. For example I searched for the payement with Id=1 with where Abetura_Processo=10 and Telefone=10 and so on, then i want the total column to be equal to 80. — Bruno Reis
– Bruno Reis, Commented May 31, 2017 at 20:34

Shadow · Accepted Answer · 2017-05-31 20:46:18Z

3

You can reference the newly inserted record's field via NEW.column_name in the before insert trigger. By setting a value to such a column, you can change the value being inserted. So, change the value setting line as follows:

SET NEW.Total = NEW.Abertura_Processo+NEW.Telefone+NEW.Correspondencia+NEW.Cartorio+NEW.Conservatorio+NEW.Servico_Financas+NEW.Taxas_Emolumentos+NEW.Honorarios;

Note, you may use generated columns as an alternative to this trigger.

answered May 31, 2017 at 20:46

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

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

Comments

Bruno Reis · Accepted Answer · 2017-06-03 11:35:14Z

0

Got it, every thing worked fine.

answered Jun 3, 2017 at 11:35

Bruno Reis

277 bronze badges

Collectives™ on Stack Overflow

Trigger Syntax mySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related