触发连接多个表 [英] Triggers to connect multiple tables

问题描述

所以我的数据库中有两个表:'test1'和'cities'.我在test1中有以下几列:'Origin'，'OriginIndex'(我只提到相关的)和城市:'city'，'value'(同样，仅相关的). 城市"表的城市"列中有5000个城市，并且在值"列中具有对应于每个城市(如伦敦，巴黎等)的双值(例如0.990、0.345、0.457、1.566).

So I have two tables in my database: 'test1' and 'cities'. I have the following columns in test1: 'Origin', 'OriginIndex' (I'm only mentioning the relevant ones), and in cities: 'city', 'value' (again, relevant ones only). The 'cities' table has 5000 cities in the 'city' column and has double values (like 0.990, 0.345, 0.457, 1.566) in the 'value' column corresponding to each city (like London, Paris, etc).

因此，假设用户在"test1"表的来源"列中输入了伦敦".在城市"表中，伦敦的城市"的值"为1.555.现在，我试图在phpmyadmin中触发一个触发器，该触发器将检查'Origin'='city'，如果是，则在插入'London'之后将相应的'value'存储在'OriginIndex'中.我创建了这样的触发器:

So let's say that a user gives an input of 'London' into the 'Origin' column of 'test1' table. In the 'cities' table, London 'city' has a 'value' of 1.555. Now, I am trying to fire a trigger in phpmyadmin which would check if 'Origin' = 'city' and if yes, the corresponding 'value' will be stored in the 'OriginIndex' after insertion of 'London'. I have created my trigger like this:

CREATE TRIGGER t1
AFTER INSERT ON test1 
FOR EACH ROW
BEGIN
UPDATE test1 a, cities b SET a.OriginIndex = b.value WHERE a.Origin = b.city;
END

但是，每次我在php网站(本地主机)"test1"表的来源"列中输入内容时，这并不是将值"存储到"OriginIndex"中.有人能找出错误吗?任何建议或提示将不胜感激！ =]

However, this is not storing the 'value' into the 'OriginIndex' every time I give inputs in 'Origin' column of 'test1' table on my php website (localhost). Can anybody figure out the error? Any advice or hint would be highly appreciated! =]

推荐答案

1. MySql不允许在触发器代码内对您拥有该触发器的同一表进行更改
2. 您可以通过更改使用BEFORE事件而不是AFTER插入的行的列值来克服此限制.
3. 现在要解决在MySql中插入的行的列值，您需要使用NEW关键字.

1. MySql doesn't allow to make changes within a trigger code to the same table on which you have that trigger
2. You can overcome this limitation by changing a column value of a row being inserted using BEFORE event rather than AFTER.
3. Now to address column values of a row being inserted in MySql you need to use NEW keyword.

话虽这么说，您的触发器应该看起来像

That being said your trigger should look like

CREATE TRIGGER tg_test1_insert
BEFORE INSERT ON test1 
FOR EACH ROW
  SET NEW.originindex = 
     (
       SELECT value 
         FROM cities 
        WHERE city = NEW.origin
     );

这里是 SQLFiddle 演示

Here is SQLFiddle demo

这篇关于触发连接多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！