Take the 2-minute tour ×
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
    
I don't understand why you would want this behaviour. It seems you want to have the 2 tables having exactly the same data (well, as long as nothing is deleted from main_table.) –  ypercube Jan 24 at 13:30
    
@ypercube I believe my opening statement declared the reason. Reworded, they are not exactly the same, one table has a shorter retention period and the other was more of an all time historical table –  atxdba Jan 24 at 20:56
    
Yes, sorry, I didn't really read the first paragraph. I couldn't manage to reproduce though the behaviour you describe. I created these identical tables and everything goes well. And I just noticed the answer ;) –  ypercube Jan 24 at 23:13

2 Answers 2

up vote 1 down vote accepted

This question came back on my radar after a recent comment was posted.

This issue at the time turned out to be this reporting table that was meant to be maintained by triggers was on a slave only.

The insert on duplicate key statements were getting flagged as not safe for statement based replication and getting pushed through in RBR (the stream was in mixed mode).

RBR events do not fire triggers.

share|improve this answer

I have tried the same what you were trying to do and I got the required result,actually there are some problems in defining the triggers.I made correction and the corrected triggers are following.Use these instead of your triggers and then perform insert or update operation,m sure your problem will be solved.

delimiter ;;

create trigger reporting_insert after insert on main_table

for each row 

  begin

    insert into reporting_table set

        id=NEW.id,

        value=NEW.value;

end;;

delimiter ;;

create trigger reporting_update after update on main_table

for each row 

  begin

    insert into reporting_table set

        id=NEW.id,

        value=NEW.value;

end;;

insert into main_table values (1,10) on duplicate key update value=value+1;

share|improve this answer

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.