Well I´m having some problems with a trigger and i don´t know how to handle this situation. Let´s start... well I have 2 tables (Table1 and Table2). Only fictional tables.
CREATE TABLE `table1` ( `id` int(10) unsigned NOT NULL auto_increment, `status` tinyint(1) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 //show create table command and CREATE TABLE `table2` ( `id` int(10) unsigned NOT NULL auto_increment, `status` tinyint(1) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1' Table1 has data with all kind of status: +----+--------+ | id | status | +----+--------+ | 51 | 0 | | 52 | 0 | | 53 | 1 | | 54 | 0 | | 55 | 2 | +----+--------+ And table 2 don´t have any data yet. I´m trying to make a trigger to get all data that it´s status = 1 from table1 and copy them to table2. And when those rows were copied into table2, other trigger changes the status from table1 with all rows that had status = 1, to 3. (sure that those tables will have more fields but now i´m just trying make a copy of the status field) So i´ve created the following trigger: CREATE TRIGGER test AFTER UPDATE ON table1 FOR EACH ROW BEGIN IF NEW.status = 1 THEN insert into table2(status) select table1.status from table1 where table1.status = 1; END IF; END; So, it works it makes a copy into table2 all rows that match the status = 1; But how i´m using AFTER UPDATE i can´t make new updates on the table1(for change the status to 3) Does anyone can help me with this situation? Or some sugestion? I´m really lost and I don´t know how to make this thing works... Thanks in advance