Page 1 of 1

Insert after update trigger

Posted: Fri Nov 17, 2006 10:29 pm
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,

Posted: Sat Nov 18, 2006 12:04 am
by feyd
Is the error a secret?

Update trigger after insert

Posted: Sat Nov 18, 2006 7:47 am
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

Posted: Sat Nov 18, 2006 8:01 am
by feyd
Is that your trigger in its entirety, unedited?

Posted: Sat Nov 18, 2006 8:47 am
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.

Insert after update trigger

Posted: Sat Nov 18, 2006 9:19 am
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

Posted: Sat Nov 18, 2006 9:30 am
by feyd

Insert after update trigger

Posted: Sun Nov 19, 2006 6:01 pm
by laknal
Hi,

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

Please give me example.