Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
thanks - I'll go with option 1 –  sunwukung Jun 25 '11 at 19:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.