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 tried adding "NOT FOR REPLICATION " in many parts in the below code but ended up in the below code. I searched a lot for an example where I can see how its done with a trigger.

Can any one show me how I can modify the below code to add option NOT FOR REPLICATION in the below code.

DELIMITER //
CREATE TRIGGER update_table
BEFORE UPDATE ON table
FOR EACH ROW
BEGIN
IF (NEW.updated_replication < OLD.updated_replication) THEN
CALL raise_error;
END IF;
SET NEW.updated_replication = NOW();
END //

Or how can I alter this trigger to make it not for replication(Alter Query). I have spent hours trying this but no results.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

You have two options

OPTION #1 : Make the Column Immutable

DELIMITER //
CREATE TRIGGER update_table
BEFORE UPDATE ON table
FOR EACH ROW
BEGIN
    IF (NEW.updated_replication < OLD.updated_replication) THEN
        NEW.updated_replication = OLD.updated_replication;
    ELSE
        SET NEW.updated_replication = NOW();
    END IF;
END //
DELIMITER ;

OPTION #2 : Break the Trigger Midstream

DELIMITER //
CREATE TRIGGER update_table
BEFORE UPDATE ON table
FOR EACH ROW
BEGIN
    IF (NEW.updated_replication < OLD.updated_replication) THEN
        SELECT dummy FROM information_schema.tables WHERE table_name='whatever';
    ELSE
        SET NEW.updated_replication = NOW();
    END IF;
END //
DELIMITER ;

The line

SELECT dummy FROM information_schema.tables WHERE table_name='whatever';

should break the trigger since there is no column called dummy in information_schema.tables

This is a technique I have written about before

GIVE IT A TRY !!!

share|improve this answer
    
Thanks for this, Why I use this trigger is that in my master master replication environment when the replication data is added to table then the latest update on that row need to survive. Will it be possible with the above trigger? I mean it must affect the replicating data. –  user2598945 Jul 2 at 14:58
    
As long as this trigger exists on both Masters, the operation should be fine. I would recommend flushing binary logs and relay logs on both Masters : dba.stackexchange.com/questions/66330/… –  RolandoMySQLDBA Jul 2 at 15:47
    
Although OPTION #2 works, OPTION #1 is better since it is not as heavy handed with handling exceptions. –  RolandoMySQLDBA Jul 2 at 15:48
    
I used the code above in my question in both the masters but it didnt worked. Where "raise_error" is an undefined procedure can just tell me why it didnt worked? Sorry just want to understand what happened. –  user2598945 Jul 2 at 16:04
    
What I am trying to do is one master updates the row at timestamp = 1(while offline) and the other updates same row with at timestamp = 2 so after some time when the connection comes back timestamp = 2 must survive..Can this be done with this OPTION #2? –  user2598945 Jul 2 at 16:07

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.