Multiple GROUP_CONCAT Statement INTO MYSQL Trigger

Question

DELIMITER |

DROP TRIGGER IF EXISTS update_trigger |

CREATE TRIGGER update_trigger
   BEFORE UPDATE ON suborder
   FOR EACH ROW
   BEGIN
   DECLARE change_value TEXT;
   DECLARE new_data TEXT;
   DECLARE changed_val BOOL;
   DECLARE data TEXT;
   DECLARE data2 TEXT;
   SET data = '';
   SET new_data = CONCAT("{", "\"order_id\"", ":", "\"", NEW.order_id, "\"");
   SET changed_val = FALSE;

   IF( NEW.order_status_id > 0 ) THEN

    IF ( OLD.suborder_id != NEW.suborder_id ) THEN
        SET changed_val = TRUE;

        SELECT GROUP_CONCAT( suborder_id SEPARATOR ',' ) INTO data,
               GROUP_CONCAT( suborder_id SEPARATOR ',' ) INTO data2
        FROM oc_suborder
        WHERE order_id = NEW.order_id AND
              suborder_id != OLD.suborder_id
        GROUP BY order_id;

        SET new_data := CONCAT(new_data, ",", "\"suborders\"", ":{");
        SET new_data := CONCAT(new_data, "\"old\"", ":", "\"",data ,",", OLD.suborder_id, "\"");
        SET new_data := CONCAT(new_data, ",\"new\"", ":", "\"",data ,",", NEW.suborder_id, "\"");
        SET new_data := CONCAT(new_data, "}");
    END IF;
    SET new_data := CONCAT(new_data, "}");



    IF (changed_val = TRUE) THEN
        INSERT INTO oc_order_change_log
        SET order_id = NEW.order_id,
            table_name = 'oc_suborder',
            change_details = new_data,
            date_added = NOW(),
            solr_synced = 0;
    END IF;

    END IF;
    END |
   DELIMITER ;

It shows as error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP_CONCAT( invoice_no SEPARATOR ',' ) INTO data2

Ankit Agrawal · Accepted Answer · 2017-02-22 06:33:06Z

1

you have syntax problem

SELECT GROUP_CONCAT( suborder_id SEPARATOR ',' ) INTO data,
               GROUP_CONCAT( suborder_id SEPARATOR ',' ) INTO data2
        FROM oc_suborder
        WHERE order_id = NEW.order_id AND
              suborder_id != OLD.suborder_id
        GROUP BY order_id;

replace with

SELECT GROUP_CONCAT( suborder_id SEPARATOR ',' ) ,
       GROUP_CONCAT( suborder_id SEPARATOR ',' ) INTO data,data2
       FROM oc_suborder
       WHERE order_id = NEW.order_id AND
              suborder_id != OLD.suborder_id
       GROUP BY order_id;

answered Feb 22, 2017 at 6:33

Ankit Agrawal

2,4541 gold badge17 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2017-02-22 06:32:50Z

1

I'm not an expert with MySQL triggers, but I believe the SELECT ... INTO syntax should look similar to an INSERT, i.e.

SELECT col1, col2 INTO var1, var2

Try this code:

IF ( OLD.suborder_id != NEW.suborder_id ) THEN
    SET changed_val = TRUE;

    SELECT GROUP_CONCAT(suborder_id SEPARATOR ','),
           GROUP_CONCAT(suborder_id SEPARATOR ',')
    INTO data, data2
    FROM oc_suborder
    WHERE order_id = NEW.order_id AND
          suborder_id != OLD.suborder_id
    GROUP BY order_id;

    SET new_data := CONCAT(new_data, ",", "\"suborders\"", ":{");
    SET new_data := CONCAT(new_data, "\"old\"", ":", "\"",data ,",", OLD.suborder_id, "\"");
    SET new_data := CONCAT(new_data, ",\"new\"", ":", "\"",data ,",", NEW.suborder_id, "\"");
    SET new_data := CONCAT(new_data, "}");
END IF;

answered Feb 22, 2017 at 6:32

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

3 Comments

Sagar Gangwal Over a year ago

I'm not an expert with MySQL triggers, You are the Expert man..!!

Tim Biegeleisen Over a year ago

@SagarGangwal Checkout Barmar's profile if you want to see a real MySQL guru :-)

Ankit Agrawal Over a year ago

@TimBiegeleisen you are my role model....i am always get inspire and knowledge from your answer

Collectives™ on Stack Overflow

Multiple GROUP_CONCAT Statement INTO MYSQL Trigger

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP_CONCAT( invoice_no SEPARATOR ',' ) INTO data2

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP_CONCAT( invoice_no SEPARATOR ',' ) INTO data2

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related