Eutherpy Eutherpy - 3 months ago 9
MySQL Question

MySQL trigger: conditional insert syntax error

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.

Answer

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