Phil Phil - 2 months ago 8
MySQL Question

Multiple definitions in a single Mysql Trigger

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][1]][1]

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;

Answer

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;
Comments