0

I have two data base A & B, in each one I have a table called answer, I want to use the 2nd one as an archive table, I want to create a trigger that copies the last inserted row in A.answer to B.answer.

Here what I did 

  CREATE TRIGGER `a` AFTER INSERT ON `A`.`answer` 
     FOR EACH ROW INSERT INTO `B`.`answer`  SELECT * FROM `answer`

This trigger works, but copy all the answers inserted in A.answer to B.answer. The problem is : I dont want to copy all answers, but only the last one. (remark : I dont know the id of the inserted answer, so dont tell me to add a ' WHERE answer.id = xx '). Thanks for your help

ethrbunny
  • 10,379
  • 9
  • 69
  • 131
Joe Kahla
  • 15
  • 4
  • 1
    Add `SELECT * FROM answer ORDER BY id DESC LIMIT 1` and that gives you the last answer. And try to be polite. – N.B. Jun 25 '13 at 14:37
  • Possible (near) duplicate of http://stackoverflow.com/questions/4753878/how-to-program-a-mysql-trigger-to-insert-row-into-another-table – ethrbunny Jun 25 '13 at 14:38
  • Nice solution N.B ... I have a 3go table, and need to order it every time I insert a new row (5000 rows inserted by minute). Thx ethrbunny – Joe Kahla Jun 25 '13 at 14:47

1 Answers1

1

You could write your trigger this way:

CREATE TRIGGER `a` AFTER INSERT ON `A`.`answer` 
FOR EACH ROW
  INSERT INTO `B`.`answer` VALUES (NEW.col1, NEW.col2, ..., NEW.colN)

where you have to specify all column names.

Please see fiddle here.

fthiella
  • 48,073
  • 15
  • 90
  • 106