触发器在mysql中乘以“基于触发器的列” [英] triggers to multiply 'trigger-based columns' in mysql

问题描述

我以前问过这个问题，我想详细说明。

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

触发器连接多个表< a>

Triggers to connect multiple tables

我有以下触发器（类似我之前问过的那种）：

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);

这部分效果很好。现在我想乘以OriginIndex和DestinationIndex并将其存储在另一列（我们说乘法）。

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

我这样做：

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);

这会产生错误'此版本的MySQL尚不支持'时间和事件。

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?

推荐答案

我认为您可以使用

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//

我希望有帮助。

这篇关于触发器在mysql中乘以“基于触发器的列”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！