store result set in temporary table, variable or separate variables within a trigger

Question

I'm in the process of creating some triggers to store the the average rating for a given product on the product table. I've also been asked to store the total number of reviews for the product. So - I need to retrieve 2 values in my trigger, and then update the target table appropriately.

Should I use separate select statements

SET my_a = SELECT a FROM foo;
SET my_b = SELECT b FROM foo;

a single statement to retrieve a result set as a variable (is this even possible?):

SET var = SELECT (a,b) FROM foo; 
UPDATE bar SET c=var.a,d=var.b;

or maybe even store the result set in a temporary table within the trigger(is that even possible?)?

Answer 1

The first method is the best because of the least overhead for the queries generated. In fact, in the MySQL Stored Procedure Language, you want as few declared variables as possible.

The second is not possible since the MySQL Stored Rrocedure Language does not have object support. A clumsy but workable UPDATE JOIN is possible if you...

1. update the question with the CREATE TABLE statement for the product table
2. show us the trigger defintion you have thus far

The third method is not possible because explicit DDL and DDL via Dynamic SQL are not allowed in MySQL Triggers.

You may have to create a regular table using either the MyISAM or MEMORY storage engine. Then, you can have the trigger compile your data to a table that actually exists. MyISAM is better because should a server go down, the compiled data thus far is on disk. MEMORY tables are faster to write to, but are gone on system restart.

DO NOT USE CREATE TEMPORARY TABLE AT ALL because such tables only last as long as the DB connection lives, and would be private unto the call of the trigger also. Even worse off, if you are using MySQL Replication and you run STOP SLAVE on the slave, any tables created via CREATE TEMPORARY TABLE disappears from the SQL Thread and replication breaks immediately when you run START SLAVE and those temp tables no longer exist.

Answer 2

This is a rather old question, so maybe this syntax didn't exist previously, but now you can just use SELECT INTO:

SELECT a, b INTO my_a, my_b FROM foo;

I find this to be aesthetically cleaner than the first syntax, and the query will only have to run once.