使用mysql触发器将行更新到另一个表 [英] Update row to another table using mysql trigger

问题描述

我在mysql数据库中有以下2个表（places& Offer（comapny），名为COUPON1.81

I have the following 2 tables(places & Offer (comapny) in mysql database named COUPON1.81

CREATE TABLE IF NOT EXISTS places (
 id int(60) NOT NULL AUTO_INCREMENT,
 namevarchar(255) NOT NULL,
Description text NOT NULL,
'Company_id' int(60) NOT NULL,
Active int(60) NOT NULL,

CREATE TABLE IF NOT EXISTS offer_company ( 
id int(60) NOT NULL AUTO_INCREMENT,
company_name varchar(150) NOT NULL,
Address text NOT NULL,
'places_id' int(60) NOT NULL,

我需要在表公司插入新行时更新表company_id到表格的行





例如，如果我向Offer_company值插入一个新行（'1'，CSB（pvt）limited，srilanka，123）那么触发器应该更新表中的行company_id到桌子位置



Places_id选择表单位置表



位置表

I need to Update the rows from table company_id to table places when insert a new row on table company


Example, if i insert a new row to Offer_company values as ('1', CSB(pvt)limited, srilanka,123) then the the trigger should to UPDATE the rows from table company_id to table places

Places_id select form places table

places table

+----------+-------------+------+-----+---------+-------+-------+-------+
| id   | name           | Description                   | Company_id  | 
+----------+-------------+------+-----+---------+-------+-------+-------+
| 123  | eiffel tower   |  CSB(pvt)limited              |  0          | 
| 124  | sigiriya       | sigiriya Rock                |  0          | 
+----------+-------------+------+-----+---------+-------+-------+-------+

报价_comapny表

Offer _comapny table

+----------+-------------+------+-----+---------+-------+
| id   | company_name     | Address      | places_id|
+----------+-------------+------+-----+---------+-------+
| 1    | CSB(pvt)limited |  89/A,Srilanka |  123    | 
+----------+-------------+------+-----+---------+-------+

在公司表格中选择地点_id后

在地方表格中，Company_id应该更新

地方表

After select places _id in company table
In Places table, Company_id should be update
places table

+----------+-------------+------+-----+---------+-------+-------+-------+
| id   | name           | Description                   | Company_id  | 
+----------+-------------+------+-----+---------+-------+-------+-------+
| 123  | eiffel tower   |  is an iron lattice tower     |  1          | 
| 124  | sigiriya       | sigiriya Rock                |  0          | 
+----------+-------------+------+-----+---------+-------+-------+-------+

i添加了这个触发器

i addedthis trigger

--
-- Triggers `offer_company`
--
DROP TRIGGER IF EXISTS `add`;
DELIMITER //
CREATE TRIGGER `add` AFTER INSERT ON `offer_company`
 FOR EACH ROW INSERT INTO places (Company_id)
    SELECT new.id
            FROM offer_company
            WHERE offer_company.places_id = new.places_id
//
DELIMITER ;

但这不起作用如何解决这个

but this not working how solve this

推荐答案

该触发器无效，因为在places表中插入新行而不是更新现有行。代码应该类似于：

That trigger isn't working because inserting new rows into the places table instead of updating existing ones. The code should be something like:

--
-- Triggers `offer_company`
--
DROP TRIGGER IF EXISTS `add`;
DELIMITER //
CREATE TRIGGER `add` AFTER INSERT ON `offer_company`
 FOR EACH ROW UPDATE places
    SET company_id = new.id
    WHERE id = new.places_id
//
DELIMITER ;

这个当然由于其他原因而存在问题，因为如果多个新公司都引用相同的地方ID，那么最后插入的那个将是存储公司ID的那个。

This of course is problematic for other reasons since if multiple new companies all refer to the same place id then whichever one is inserted last will be the one who's company id is stored.

这篇关于使用mysql触发器将行更新到另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！