具有内部联接的MySQL触发器 [英] MySQL Trigger with a Inner Join

问题描述

我在mySQL中有两个表，称它们为评论和产品.然后，我还有另一个表(称为productsmaster)，该表是评论和产品的内部连接.我正在尝试创建一个触发器，以在将新记录添加到评论时运行内部联接查询.

I have two tables in mySQL, call them reviews and products. And then I have another table (call it productsmaster) that is an inner join of reviews and products. I am trying to create a trigger to run the inner join query when a new record is added to reviews.

我试图将内部联接查询插入到触发器中，但是它返回#1422-存储的函数或触发器中不允许显式或隐式提交".错误.为清楚起见，我的触发器代码为:

I have tried to just insert the inner join query into the trigger, but it is returning a "#1422 - Explicit or implicit commit is not allowed in stored function or trigger." error. For clarity of the issue my code for the trigger is:

CREATE TRIGGER updateprodmaster 
AFTER INSERT ON reviews 
FOR EACH ROW 
BEGIN 
CREATE TABLE productsmaster AS
SELECT products.*, reviews.userid, reviews.usergender, reviews.userage, reviews.score
FROM products
INNER JOIN reviews
ON products.pid=reviews.pid;
END;$$

如果有人对此有任何想法，将不胜感激.谢谢！

If anyone has any thoughts on this it would be much appreciated. Thanks!

杰克

推荐答案

CREATE TABLE 语句导致隐式的COMMIT.那是不允许的.

对此限制没有简单的按钮变通方法.

There are no easy button workarounds to this limitation.

但是，即使您能够解决此限制，为什么还要在塑料中每次插入一行时尝试创建一个新表?

But even if you were able to workaround this limitation, why in the plastic would you want to attempt to create a new table every time a row is inserted?

插入第二行时，触发器将尝试(再次)创建一个完全相同名称的表(由于该名称的表已存在，因此将失败.)

When a second row is inserted, the trigger would to attempt to (again) create a table of the exact same name (which will fail because a table of that name already exists.)

将马车往后退一点.

弄清楚您需要满足什么要求.

And figure out what requirement you need to meet.

当您回到需要触发器的位置时，就可以烧掉它.

When you get back to needing a trigger, you can burn that bridge when you get to it.

关注

如果目的是尝试将行插入到productsmaster表中，则每当使用插入后触发器将行插入到reviews表中时，我们都需要在触发器主体中使用INSERT语句

If the intent is to attempt to insert a row into productsmaster table, whenever a row is inserted into the reviews table, using an after insert trigger, we'd need an INSERT statement in the trigger body.

该行的列值(刚刚插入到reviews中)在触发器中可用.无需从reviews表中进行选择.通过使用 NEW.

The values of the columns of the row (that was just inserted to reviews) are available in the trigger. There's no need to select from the reviews table. We can reference the column values of the newly inserted row (in an after insert trigger) by qualifying the column names with NEW.

我建议避免使用 .* ，并明确命名要从products检索的列.我假设pid列是products中的唯一键(或主键).

I recommend avoiding .*, and explicitly name the columns to be retrieved from products. I'm assuming the pid column is a unique key (or the primary key) in products.

例如:

 DELIMITER $$

 CREATE TRIGGER trg_reviews_after_insert 
 AFTER INSERT ON reviews
 FOR EACH ROW
 BEGIN
    INSERT INTO productsmaster (col1, col2, userid, usergender, userage, score)
    SELECT p.col1
         , p.col2
         , NEW.userid
         , NEW.usergender
         , NEW.userage
         , NEW.score
      FROM products p
      WHERE p.pid = NEW.pid;
  END$$

 DELIMITER $$

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