MySQL trigger: conditional insert syntax error

Question

I have two tables: Person and Student. I am trying to write a trigger which, after inserting a new person, inserts into the student table if the person is less than 18 years old.

Here is the code of the trigger:

CREATE TRIGGER test_trigger
AFTER INSERT
ON Person
FOR EACH ROW
BEGIN
insert into `Student`(TestValue, ID) 
values("Test", select Person.ID from Person where Person.DateOfBirth > curdate() - interval 18 year);
END

But I keep getting the

You have an error in your SQL syntax... near select Person.ID from...interval 18 year.

error, and I don't know if it's because of the conditional inserting, or the datetime operation, or something else.

score 2 · Answer 1 · answered Aug 29 '16 at 22:16

You wanted to use insert into ... select from construct like below and can't mix both insert into .. values construct with

insert into `Student`(TestValue, ID) 
select "Test", Person.ID 
from Person 
where Person.DateOfBirth > curdate() - interval 18 year;

score 1 · Accepted Answer · answered Aug 29 '16 at 22:16

Should be:

CREATE TRIGGER test_trigger
AFTER INSERT
ON Person
FOR EACH ROW
BEGIN
insert into `Student`(TestValue, ID) 
select "Test", Person.ID from Person where Person.DateOfBirth > curdate() - interval 18 year;
END

You can't have a SELECT clause as an argument in VALUES. But you can have "Test" as a value in your select...

MySQL trigger: conditional insert syntax error

2 Answers2