MySQL AFTER INSERT Trigger - select INTO using NEW values not working

Question

In MySQL:

I have an interview table, and
when a new interview is created (a row inserted into interview),
I want to add rows to a child table interview_questions table linking to the question definitions in interview_questions_template.

I'm trying to do this in an AFTER INSERT trigger on interview, and mysql is saying I have a syntax error at the end of my INSERT INTO ... SELECT statement.

I've tried joining with NEW, thinking it might be a table, but that didn't work either. Gander at the code?

CREATE TRIGGER interview_AFTER_INSERT AFTER INSERT ON interview 
FOR EACH ROW
BEGIN
  INSERT INTO interview_questions (id_interview, id_candidate, id_question_def,  s_userid)
    SELECT NEW.id_interview, NEW.id_candidate, interview_question_template.id_question_def, NEW.s_userid 
        FROM interview_question_template;

END

The error mysqlworkbench is showing is "missing 'semicolon'", underlining interview_question_template after the FROM.

The execution error says there is an error on that line after ' '.

Community · Accepted Answer · 2017-05-23 12:23:42Z

1

You are doing fine. Just wrap the whole thing in a delimiter block. The below survives the 1064 Error.

DELIMITER $$
CREATE TRIGGER interview_AFTER_INSERT AFTER INSERT ON interview 
FOR EACH ROW
BEGIN
  INSERT INTO interview_questions (id_interview, id_candidate, id_question_def,  s_userid)
    SELECT NEW.id_interview, NEW.id_candidate, interview_question_template.id_question_def, NEW.s_userid 
        FROM interview_question_template;

END
$$
DELIMITER ;

As for the importance of Delimiters, see the bottom of This Post of mine. I wrote it more eloquently elsewhere, just can't find a reference to it. And the mysql manual has little about it with any verbosity for the average human.

I am sorry for posting the obvious. Sometimes people just have to see it :P

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Dec 2, 2015 at 19:16

Drew

25k10 gold badges47 silver badges81 bronze badges

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

1 Comment

Pól Over a year ago

That did it! Thanks!

Pól · Accepted Answer · 2015-12-02 19:26:45Z

0

The other thing I did was to simply remove the BEGIN/END and it also worked like a charm.

answered Dec 2, 2015 at 19:26

Pól

13914 bronze badges

Collectives™ on Stack Overflow

MySQL AFTER INSERT Trigger - select INTO using NEW values not working

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related