Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.