0

I have 2 identical table (100% identical), 

DELIMITER $$

CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `db`.`new_user` AFTER INSERT
ON `db`.`user`
FOR EACH ROW BEGIN
INSERT INTO db2.`users` COPY ALL INSERTED DATA
END$$

DELIMITER ;

How should the INSER query look like? Do I have to specifiy all field names one by one?

Johan Larsson
  • 175
  • 5
  • 17
  • Either explicitly mention each column in the `VALUES` clause of the `INSERT` statement, or else use `INSERT ... SELECT` whilst filtering on the `NEW.primary_key`. – eggyal Mar 14 '13 at 20:12
  • 1
    This is the same question: http://stackoverflow.com/questions/57168/how-to-copy-a-row-from-one-sql-server-table-to-another provides a great answer – Lainezor Mar 14 '13 at 20:31

1 Answers1

2

Since NEW is not a row identifier but rather a syntactic way for referring to particular columns in a row being manipulated by a trigger, you need to specify column names

INSERT INTO db2.`users` VALUES(NEW.id, NEW.username, ...);
peterm
  • 91,357
  • 15
  • 148
  • 157