Insert row into another table via mySQL trigger

Question

I'm getting a syntax error when trying to create a trigger to insert a row from one table into another...updating the date and time in the process. Here's my query:

INSERT INTO `second_table` (field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11, field12)
  (SELECT `field1`, `field2`, `field3`, `field4`, `field5`, `field6`, `field7`, `field8`, `field9` FROM `first_table` WHERE `field1` = NEW.`field1`), CURDATE(), NOW(), (SELECT `field12` FROM `second_table` WHERE `field1` = NEW.`field1`);

This trigger will run when first_table is updated. I'm not sure if this is the correct way to accomplish the task. I've generalized the field and table names for simplicity.

EDIT:

The columns are identical between the two tables. I just want the ability to update the date and time when the trigger occurs. This works, but obviously just copies the previous date and time:

INSERT INTO `second_table` (SELECT * FROM `first_table` WHERE `field1` = NEW.`field1`);

Not sure what you mean...the columns are identical between the two tables, I just want to be able to grab the current date and time when the trigger occurs. — William Orazi
– William Orazi, Commented Mar 21, 2013 at 18:22
It doesn't matter. It's poor practice. What happens if the destination table changes column order or adds/removes a column? — Kermit
– Kermit, Commented Mar 21, 2013 at 18:23
I see what you're saying, and I'll make that change once I can figure out how to get the updated data that I need. — William Orazi
– William Orazi, Commented Mar 21, 2013 at 18:24

scawp · Accepted Answer · 2013-04-10 16:21:44Z

2

Try:

INSERT INTO `second_table` (field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11, field12)
 (SELECT `field1`, `field2`, `field3`, `field4`, `field5`, `field6`, `field7`, `field8`, `field9`, CURDATE() AS `field10`, NOW() AS `field11`, `field12` FROM `first_table` WHERE `field1` = NEW.`field1`);

answered Apr 10, 2013 at 16:21

scawp

361 bronze badge

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

Collectives™ on Stack Overflow

Insert row into another table via mySQL trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related