MySQL Trigger with a Inner Join

Question

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.

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!

Jack

Answer 1

The CREATE TABLE statement causes an implicit COMMIT. And that's not allowed.

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

Back the cart up a bit, behind the horse.

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.

FOLLOWUP

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.

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.

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.

As an example:

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