CODEWITHSUNDEEP
mysqltriggersgroup-concat
Sudhanshu Jain
Sudhanshu Jain

Reputation: 534

Multiple GROUP_CONCAT Statement INTO MYSQL Trigger

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

Upvotes: 0

Views: 425

Answers (2)

Ankit Agrawal
Ankit Agrawal

Reputation: 2454

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;

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522226

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;

Upvotes: 1

Related Questions