Error 1064 when creating Trigger

Question

I am using MYSQL 5.6 and phpMyAdmin 4.1.14

I am trying to create a Trigger that will copy data from one db to another but get the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'new.airplane_id,new.user_id,new.icao24,new.created_at,new.timestamp,new.altitude' at line 7

Any pointers would be gratefully received

My code is:

CREATE TRIGGER airfields_insert 
BEFORE INSERT 
ON realtime 
FOR EACH ROW
BEGIN
if (new.squawk between 6160 and 6167 or new.squawk between 6171 and 6177) and new.altitude <= 4000 then 
insert into airfields (airplane_id,user_id,icao24,created_at,timestamp,altitude,flight_number,latitude,longitude,heading,squawk,horizontal_speed,vertical_speed,Interested) (new.airplane_id,new.user_id,new.icao24,new.created_at,new.timestamp,new.altitude,new.flight_number,new.latitude,new.longitude,new.heading,new.squawk,new.horizontal_speed,new.vertical_speed,new.Interested);
end if;

Besides VALUES keyword you are also missing END and you need DELIMITER — Mihai
– Mihai, Commented Jul 13, 2015 at 10:18

Ajmal Muhammad · Accepted Answer · 2015-07-13 09:53:41Z

2

You have Syntax error in insert query Your QUERY should be like that

insert into airfields (airplane_id,user_id,icao24,.......) 
values (new.airplane_id,new.user_id,new.icao24,........);

or simply

 insert into airfields values (new.airplane_id,new.user_id,new.icao24,........);

The form does not specify the column names where the data will be inserted, only their values:

answered Jul 13, 2015 at 9:53

Ajmal Muhammad

6957 silver badges25 bronze badges

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

Comments

Rahul · Accepted Answer · 2015-07-13 09:43:46Z

0

You are missing the VALUES keyword in your INSERT statement

Your current INSERT query

insert into airfields (airplane_id,user_id,icao24,.......) 
(new.airplane_id,new.user_id,new.icao24,........);
end if;

It should be

insert into airfields (airplane_id,user_id,icao24,.......) 
values (new.airplane_id,new.user_id,new.icao24,........);
end if;

Also, you are missing the END keyword after END IF

answered Jul 13, 2015 at 9:43

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

1 Comment

Dave Guy Over a year ago

Thanks very much for your answer Rahul. Unfortunately this does not do what I want. Once in effect, the trigger stops anything else going into the original table (realtime). I think that I will have to re-think the whole thing. Perhaps a trigger is not what I need for what I am trying to achieve.

Collectives™ on Stack Overflow

Error 1064 when creating Trigger

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related