TRIGGER AFTER UPDATE - copy to another table

Question

I would like to create a TRIGGER AFTER UPDATE which copy the row that has just been updated in TABLE_1 to the TABLE_2.

This code raise an error after updated more than one row : SQL Error (1062): Duplicate entry '1' for key 'PRIMARY'

I know this code is not right but I just don't know how to manipulate the row that has been updated.

CREATE DEFINER=`companyERP`@`%` TRIGGER `trigger` AFTER INSERT ON `Table_1`
FOR EACH ROW BEGIN
INSERT INTO Table_2 SELECT *
FROM Table_1;
END

The problem here is that you have copied the same structure for table_1 to table_2 including the primary key. Table_2's primary key needs to be a separate column not available in table_1. — Adish, Oct 30 '15 at 17:20
http://stackoverflow.com/questions/4753878/how-to-program-a-mysql-trigger-to-insert-row-into-another-table — Mark Leiber, Oct 30 '15 at 17:47

score 0 · Answer 1 · answered Oct 30 '15 at 18:20

0

CREATE DEFINER=`companyERP`@`%` TRIGGER `trigger` AFTER INSERT ON `Table_1`
FOR EACH ROW BEGIN
INSERT INTO Table_2 SELECT new.Field1, newField2 ...
FROM Table_1
END

answered Oct 30 '15 at 18:20

H.K

173
8

score 0 · Answer 2 · answered Oct 31 '15 at 18:59

Try:

/*
CREATE DEFINER=`companyERP`@`%` TRIGGER `trigger` AFTER INSERT ON `Table_1`
FOR EACH ROW BEGIN
INSERT INTO Table_2 SELECT *
FROM Table_1;
END
*/

DELIMITER //

CREATE TRIGGER `trigger` AFTER UPDATE ON `Table_1`
FOR EACH ROW
BEGIN
  INSERT INTO `Table_2`
    (`id_table_1`, `old_value`, `created_at`)
  VALUES
    (OLD.`id`, OLD.`value`, NOW());
END//

DELIMITER ;

SQL Fiddle demo

TRIGGER AFTER UPDATE - copy to another table

2 Answers2