MySql create trigger error 1064

Question

the sql I wrote is as below:

create

trigger test_schedule_trigger after update on tw_task
as
update tw_test_schedule test, tw_task task
set test.name = task.name,test.`type`=task.`type`,test.is_deleted=task.is_deleted,test.gmt_modified=task.gmt_modified;
where tw_test_schedule.task_id = tw_task.id;

the error description is:

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 'where tw_test_schedule.task_id = tw_task.id' at line 1

Mohamad Arafat · Accepted Answer · 2015-08-07 06:48:38Z

1

Try remove the ; before the WHERE

answered Aug 7, 2015 at 6:48

Mohamad Arafat

5731 gold badge5 silver badges25 bronze badges

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

Comments

paxdiablo · Accepted Answer · 2015-08-07 06:51:50Z

0

You have a ; semicolon at the end of your set line:

                                                right here
                                                     v
set blah, blah, test.gmt_modified = task.gmt_modified;
where blah ...

This is terminating the statement, meaning that the where is not really valid SQL. Just get rid of that semicolon.

edited Aug 7, 2015 at 6:51

answered Aug 7, 2015 at 6:48

paxdiablo

888k243 gold badges1.6k silver badges2k bronze badges

4 Comments

eric.zl Over a year ago

I tried as you suggested, howerer it remained unsolved. The description after I modified is "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 'asupdate tw_test_schedule test, tw_task task set test.name = task.name,test.`' at line 3"

paxdiablo Over a year ago

@eric, then check your string near that point. I'm pretty certain asupdate is not valid SQL either :-)

eric.zl Over a year ago

sorry for my ignorance, i'm a newcomer of mysql. i made a modification but it turned out to commit the same mistake.

create trigger test_schedule_trigger after update on tw_task for each row begin 	update tw_test_schedule test, tw_task task 	set test.name = task.name,test.

type=task.type,test.is_deleted=task.is_deleted,test.gmt_modified=task.gmt_modified where test.task_id=task.id end;The description is 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 'end' at line 8.

paxdiablo Over a year ago

@eric, you don't need begin/end in this case because it's a single statement. But, if you do use it, every statement within it must be terminated with a semicolon. That means putting one before the end.

Collectives™ on Stack Overflow

MySql create trigger error 1064

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related