MYSQL SELECT INSERT inside Trigger

Question

    delimiter //
CREATE TRIGGER trig AFTER INSERT ON table0 FOR EACH ROW 
BEGIN
    CREATE TEMPORARY TABLE temp (SELECT t1.col1,t1.Col2,t2.col3,t3.col4,t4.col5 FROM (table1 AS t1 JOIN table2 AS t2 ON t1.col1 = t2.col3... etc) WHERE t1.condition = NEW.condition);
    INSERT INTO table5 VALUES (NULL,CURRENT_TIMESTAMP(),0,0,NULL,NULL,NULL,NEW.condition,   SELECT GROUP_CONCAT("\"",someColumn - 1,",",50,",",someColumn2,",",30,",",someColumn3,"\"" SEPARATOR ';') FROM temp);
    DROP temp;
END;//
delimiter ;

The error is now

ERROR 1064 (42000): 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 'SELECT CONCAT("\"",someColumn - 1,",",50,",",someColumn2,",",30,",",someColu' at line 4

It gets cut off at the someColu of someColumn3

Thank you for your time.

Generally bad style in my opinion to omit the field list from an INSERT; and very poor form to post a question with such an insert without providing such information for the table (tableZ in this case). — Uueerdo
– Uueerdo, Commented Aug 13, 2015 at 19:11
Can you try by changing "\"" to '"' in your insert statement? — Pavan Andhukuri
– Pavan Andhukuri, Commented Aug 14, 2015 at 6:58

Pavan Andhukuri · Accepted Answer · 2015-08-13 18:48:16Z

1

You seem to miss line delimiters. You need to add them.

delimiter //
CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
    IF NEW.amount < 0 THEN
        SET NEW.amount = 0;
    ELSEIF NEW.amount > 100 THEN
        SET NEW.amount = 100;
    END IF;
END;//
delimiter ;

It is mandatory to change the delimiter to something other than ';' so that the execution does not terminate in the middle of create trigger statement.

Here we are changing the delimiter to '//' just before creating the trigger and changing it back to ';' immediately.

answered Aug 13, 2015 at 18:48

Pavan Andhukuri

1,6273 gold badges28 silver badges49 bronze badges

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

Collectives™ on Stack Overflow

MYSQL SELECT INSERT inside Trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related