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