I have two tables with identical schemas. one is a "main" table that is pruned periodically and kept small. The other is a "reporting" table that is an archive of everything that ever was. For simplicity lets say the table schemas look like
create table main_table( pk int unsigned not null primary key, value int unsigned ); create table reporting_table( pk int unsigned not null primary key, value int unsigned );
I have two triggers, one AFTER INSERT, and one AFTER UPDATE that look like
create trigger reporting_insert after insert on main_table for each row insert into reporting_table (id,value) VALUES (NEW.id, NEW.value); create trigger trigger reporting_update after update on main_table for each row update reporting_table set value=NEW.value where id=NEW.id;
The main table is populated with queries like
insert into main_table values (1,10) on duplicate key update value=value+1;
My problem is the reporting table is not catching this ODKU +1 for the value (and it clearly is updating in the main table).
From the trigger docs:
A potentially confusing example of this is the INSERT INTO ... ON DUPLICATE KEY UPDATE ... syntax: a BEFORE INSERT trigger will activate for every row, followed by either an AFTER INSERT trigger or both the BEFORE UPDATE and AFTER UPDATE triggers, depending on whether there was a duplicate key for the row.
From reading that I would think it would follow the latter path executed any Before/After update triggers. Doing a straight UPDATE query on main_table does result on the changes getting propagated to the reporting table.
What am I missing?