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.

Rahul · Accepted Answer · 2016-08-29 22:16:50Z

2

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;

answered Aug 29, 2016 at 22:16

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

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

Comments

Galz · Accepted Answer · 2016-08-29 22:16:29Z

1

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...

answered Aug 29, 2016 at 22:16

Galz

6,8525 gold badges35 silver badges39 bronze badges

Collectives™ on Stack Overflow

MySQL trigger: conditional insert syntax error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related