MySQL – How to Create Trigger with NOT FOR REPLICATION

database-designMySQLtrigger

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.

Best Answer

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?

GIVE IT A TRY !!!

Related Solutions

Sql-server – Create a trigger that compares a field, with a field in other table

You can't create triggers on system objects. So going down the path you're thinking isn't possible.

Your best bet to increase fault tolerance is to move your Witness database to it's own server hardware, (not just another instance on the same box even though SQL Server allows you to set it up that way), separate from both the principal and the mirror.

That way you're highly unlikely to have a failure that takes out both your principal AND your mirror database at the same time.

Mysql – How to make a trigger that will compare input with a value of other table

DELIMITER $$  
CREATE TRIGGER Bookings_BeforeInsert BEFORE INSERT ON Bookings
FOR EACH ROW  
BEGIN  
    DECLARE found_flight,dummy,baddata INT;
    DECLARE dt DATETIME;
    SET baddata = 1;
    SELECT COUNT(1) INTO found_flight FROM Flights
    WHERE flight_number = NEW.fk_flight_number;
    IF found_flight > 0 THEN
        SELECT date_of_departure INTO dt FROM Flights
        WHERE flight_number = NEW.fk_flight_number;
        IF date_of_reservation < date_of_departure THEN
            SET baddata = 0;
        END IF;
    END IF;
    IF baddata = 1 THEN  
        SELECT CONCAT('Cannot Insert This Because Age ',NEW.age,' is Invalid')  
        INTO dummy FROM information_schema.tables;
    END IF;  
END; $$  
DELIMITER ;

The IF statement IF baddata = 1 THEN was made a bogus message on purpose. That's because MySQL error handling for Stored Procedures is not designed properly. This crazy workaround I got from Pages 254-256 of the book MySQL Stored Procedure Programming

enter image description here

under the subheading 'Validating Data with Triggers':

I have written about this style of Trigger Programming in my past posts

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