I need to populate a MySQL table with trigger. It read from a table and copy data to an other. This is the trigger:
CREATE DEFINER=root@localhost PROCEDURE create()
BEGIN
delete from open;
insert into opent Select * from Open2 where open=1;
END$$
DELIMITER ;
It happened that an other process tried to select * rows from table open and found it empty. How can I prevent other query to select the table open while the trigger is executing?
I try to explain better. I have many clients connected to a table only with select. So there is a php page that select all records from TableA. TableA is created by a Trigger that copy data from TableB to TableA after some record is updated. The problem is that when the trigger works, it has to delete all row from TableA and insert again. If the php is called in the meantime it finds a partial table. I really don't know where to use table lock. In the trigger or using select for update in the php page?
