How to create trigger with NOT FOR REPLICATION

Question

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.

score 0 · Accepted Answer · edited Apr 13 '17 at 12:42

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

Apr 25, 2011 : Trigger in MySQL to prevent insertion
Dec 23, 2011 : check constraint does not work?

How to create trigger with NOT FOR REPLICATION

1 Answers1

OPTION #1 : Make the Column Immutable

OPTION #2 : Break the Trigger Midstream

GIVE IT A TRY !!!