Insert after update trigger

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
laknal
Forum Newbie
Posts: 21
Joined: Wed Oct 11, 2006 7:38 pm

Insert after update trigger

Post by laknal »

Hi,

I need some help with the following trigger (I am getting error at INSERT INTO line):

I have two tables: orders and order tracking.

Once the user update the order, I want to insert the data in order tracking table.

Order table fields that I want to insert in order tracking are: order_id, update_start, user_comments, updateStatus_id

Order tracking fields : order_track_id (auto increment), order_id, Start_time, comments, status_id

Code: Select all

CREATE TRIGGER auInsertrack

AFTER UPDATE ON 

order FOR EACH ROW BEGIN 

INSERT INTO order_tracking SET  comments=NEW.user_comments,

Start_time=NEW.update_start, status_id=NEW.updateStatus_id 

Where order_tracking.order_id=NEW.order_id
Any help is appreciated.

Thanks,
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Is the error a secret?
laknal
Forum Newbie
Posts: 21
Joined: Wed Oct 11, 2006 7:38 pm

Update trigger after insert

Post by laknal »

Hi,

Below is the error:


MySQL said:
#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 'INSERT INTO order_tracking SET
comments=NEW.user_comments,
Start_time=NEW.st' at line 4
Thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Is that your trigger in its entirety, unedited?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

CREATE TRIGGER auInsertrack

AFTER UPDATE ON

order FOR EACH ROW BEGIN
order is a reserved word.
Either change the name of the table or use `order`
And INSERT cannot have a WHERE clause.
laknal
Forum Newbie
Posts: 21
Joined: Wed Oct 11, 2006 7:38 pm

Insert after update trigger

Post by laknal »

Hi,

I changed order to orders, but I am still getting the same error.

what is the correct syntax for insert upon update trigger ?

Thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

laknal
Forum Newbie
Posts: 21
Joined: Wed Oct 11, 2006 7:38 pm

Insert after update trigger

Post by laknal »

Hi,

I did not find example for mulitple columns update upon insert.

Please give me example.
Post Reply