CODEWITHSUNDEEP
mysql
Dark Shadow
Dark Shadow

Reputation: 35

Copy all data from one table to another via trigger | phpmyadmin

I'm pretty new to this whole trigger thing with PHPMyAdmin. I don't really know what I am doing wrong but I want simply to copy all the data from table1 to table2 like an archive or something like that. Everytime there is an insert it should copy the data to table2.

Table1 has these attributes/fields:

ID
customerID
BookSN(SN = serialnumber)
created_at(when he lend the book)
updated_at(when he gave the book back)

Table2:

Has the same attributes/fields as Table1

I'm trying to solve this:

delimiter //
CREATE TRIGGER `simple_copy` AFTER INSERT ON table1
FOR EACH ROW BEGIN
insert into table2(id, customerID, BookSN, created_at,updated_at)
select ?? -- i dont know what to write here...
from table1;
END;
//
delimiter ;

I would be really grateful if you can help me to fix this total mess.

Upvotes: 0

Views: 328

Answers (2)

pspatel
pspatel

Reputation: 518

If you want to copy all columns you have to use like this

delimiter #
CREATE TRIGGER `simple_copy` AFTER INSERT ON table1
FOR EACH ROW 
BEGIN
insert into table2(id, customerID, BookSN, created_at,updated_at)
values (new.id, new.customerID, new.BookSN, new.created_at,new.updated_at)
from table1;
END#
delimiter ;

Upvotes: 0

cdaiga
cdaiga

Reputation: 4937

If there are some data in table1 that you would want to copy in table2 then first run this:

INSERT INTO table2(id, customerID, BookSN, created_at, updated_at)
SELECT id, customerID, BookSN, created_at, updated_at
FROM table1;

Then you can now use a trigger to continuously copy the newly inserted data from table1 into table2.

DELIMITER //
CREATE TRIGGER `simple_copy` AFTER INSERT ON table1
FOR EACH ROW BEGIN
INSERT INTO table2(id, customerID, BookSN, created_at, updated_at)
VALUE (new.id, new.customerID, new.BookSN, new.created_at, new.updated_at);
END//
DELIMITER ;

But if table1 is empty then just go at once with the trigger.

Upvotes: 1

Related Questions