CODEWITHSUNDEEP
mysqlrelational-databasedatabase-trigger
Eutherpy
Eutherpy

Reputation: 4571

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.

Upvotes: 0

Views: 37

Answers (2)

Rahul
Rahul

Reputation: 77876

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;

Upvotes: 2

Galz
Galz

Reputation: 6832

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

Upvotes: 1

Related Questions