2

Is it possible to trigger the insert of all the columns for the NEW row? Basically, I want to make a copy of the newly insert record in another table but what having to specify all the columns.

Something like

DELIMITER $$

CREATE TRIGGER TestTrigger
AFTER INSERT
   ON Table1 FOR EACH ROW

BEGIN
    INSERT INTO `Table2` SELECT * FROM NEW;
END$$
DELIMITER ;

However this returns Table 'Database.NEW' doesn't exist whenever I try to insert a new row in Table1.

From MySQL documentation:

Within the trigger body, you can refer to columns in the subject table (the table associated with the trigger) by using the aliases OLD and NEW. OLD.col_name refers to a column of an existing row before it is updated or deleted. NEW.col_name refers to the column of a new row to be inserted or an existing row after it is updated.

Wistar
  • 3,770
  • 4
  • 45
  • 70
  • @lad2025 That doesn't work MySQL returns "Cannot find table DatabaseName.NEW" – Wistar Nov 15 '15 at 22:02
  • 1
    Anyway specify all column names manually because `SELECT *` is bad practice 9/10 cases – Lukasz Szozda Nov 15 '15 at 22:05
  • 2
    I actually disagree. I keep seeing people say doing x is a bad practice but x exists to be used and just because some people abuse doesn't mean it's bad practice. `SELECT *` is exactly what you should use if you do want **ALL** the columns – Arijoon Nov 15 '15 at 22:44
  • @Arijoon Thank you for your comment. Please see the edited question – Wistar Nov 15 '15 at 23:02
  • is `NEW` a row or a table name? It has to be a table name. `SELECT table_name` is the right syntax and only supports table names – Arijoon Nov 15 '15 at 23:05
  • @Arijoon No it's not. I am referring to the newly inserted row. I edited the post with more information – Wistar Nov 15 '15 at 23:09
  • `SELECT` is used to grab rows with matching columns or `*`. You do not need `SELECT` since you already have your row – Arijoon Nov 16 '15 at 09:40
  • @Arijoon I understand that but `INSERT INTO Table2 NEW` is syntactically incorrect. Do you have any suggestion? – Wistar Nov 16 '15 at 19:38
  • Look at this answer: http://stackoverflow.com/questions/6354132/insert-data-into-table-with-result-from-another-select-query – Arijoon Nov 17 '15 at 09:22

1 Answers1

2

Despite the time this question have been unanswered, you can SELECT all fields using primary key in source table if you have one.

CREATE TRIGGER replicated_table AFTER INSERT ON source_table FOR EACH ROW
BEGIN
    INSERT INTO replicated_table SELECT * FROM source_table WHERE id=NEW.id;
END

Also, maybe you can prefer the use of REPLACE instead of INSERT to ensure the table will not stay out of sincronization.

mr.xkr
  • 51
  • 4
  • There is almost same issue here: https://stackoverflow.com/questions/15419227/mysql-trigger-to-copy-all-field-values-to-another-table-how and same solution could be applied – mr.xkr Oct 09 '17 at 11:02