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
