mysql 乘法触发器,触发器在mysql中乘以“基于触发器的列”

I have asked this question before and I want to elaborate it.

I have the following trigger (kind of like the one I asked before):

CREATE TRIGGER trigger_test1

BEFORE INSERT ON test1

FOR EACH ROW

SET NEW.OriginIndex = (SELECT index1 FROM cities WHERE city = NEW.Origin),

NEW.DestinationIndex = (SELECT index1 FROM cities WHERE city =NEW.Destination);

This part works well. Now I want to multiply OriginIndex and DestinationIndex and store it in another column (let's say 'Multiplication').

I did this:

CREATE TRIGGER trigger_test1

BEFORE INSERT ON test1

FOR EACH ROW

SET NEW.OriginIndex = (SELECT index1 FROM cities WHERE city = NEW.Origin),

NEW.DestinationIndex = (SELECT index1 FROM cities WHERE city =NEW.Destination),

NEW.Multiplication = (SELECT NEW.OriginIndex*NEW.DestinationIndex);

This gives an error 'This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table''.

How am I supposed to approach this problem?

解决方案

I think you can use

DELIMITER //

CREATE TRIGGER trigger_test1

BEFORE INSERT ON test1

BEGIN

FOR EACH ROW

SET NEW.OriginIndex = (SELECT index1 FROM cities WHERE city = NEW.Origin);

SET NEW.DestinationIndex = (SELECT index1 FROM cities WHERE city =NEW.Destination);

SET NEW.Multiplication = (NEW.OriginIndex*NEW.DestinationIndex);

END//

I hope this help.