CODEWITHSUNDEEP
mysqltriggersinsert
Wistar
Wistar

Reputation: 3790

MySQL trigger INSERT all NEW columns

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.

Upvotes: 2

Views: 3983

Answers (1)

mr.xkr
mr.xkr

Reputation: 51

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.

Upvotes: 2

Related Questions