I have created a multi-page survey form that collects a value of 1 to 10 on each page.
With that data I need to insert 3 different calculations into 3 different columns in the database.
I have created a trigger that adds up all the numbers from the 20 pages and inserts it into the total score column but I need 2 other subscale scores and my version of mysql limits 1 trigger with the same action per table.
is it possible to have one trigger that can insert values into 3 different columns?
I am trying to do this via phpMyAdmin
My deinition below that works for total score:
[![enter image description here]]
SET NEW.total_score = NEW.answer_01 + NEW.answer_02 + NEW.answer_03 + NEW.answer_04 + NEW.answer_05 + NEW.answer_06 + NEW.answer_07 + NEW.answer_08 + NEW.answer_09 + NEW.answer_10 + NEW.answer_11 + NEW.answer_12 + NEW.answer_13 + NEW.answer_14 + NEW.answer_15 + NEW.answer_16 + NEW.answer_17 + NEW.answer_18 + NEW.answer_19 + NEW.answer_20;
The mysql site - http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html
mysql> CREATE TRIGGER ins_transaction BEFORE INSERT ON account -> FOR EACH ROW PRECEDES ins_sum -> SET -> @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0), -> @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);
comma separating the columns seems to be the solution
So for your code it would be like...
SET NEW.total_score = NEW.answer_01 + ... + NEW.answer_20, NEW.other_column = NEW.answer_01 + NEW.answer_02;