Mysql Trigger after update only if row has changed

The truth about timestamps

Using the timestamp field as an indicator of actual change can be tempting. However, MySQL updates timestamp on every row modification, regardless of any meaningful changes. To avoid unnoticed triggers on sub-second changes, you should still compare each column's NEW and OLD values.

Remember, the trigger dines only on real updates - not mere statements or unchanged data. Maintaining its diet would mean that the timestamp remains the same when no change occurs, else it updates along with the trigger's magic.

Performance matters: Optimize your triggers

Crafting efficient triggers can save your database from a speed-breaker hit. Particularly when you've a field day with multi-row updates, ensure your trigger logic doesn't trip over.

Use a WHERE clause in UPDATE statements whenever its crucial to skip over rows that don't need updating, effectively reducing the trigger runs on unaltered data. And let's not forget, revising the trigger logic keeps it in sync with any database structure alterations, ensuring vital signs of performance don't flatline.

Dealing with NULL values

Weaving NULL values correctly into your trigger story can be a bit of a tightrope walk. Introducing the superhero of the day - the <=> operator. Unlike the plain-jane = operator, <=> ensures two NULL values are always treated as equal. When scouting for changes, it's crucial to remember to use the NOT operator with <=>.

IF NOT (NEW.column1 <=> OLD.column1)


Change is constant, so log it

Why just run an action, when you can chronicle the change? Logging changes allow you to have a history of alterations, archived for audits or rollbacks. Here's a way to make a note:

CREATE TRIGGER update_trigger
AFTER UPDATE ON target_table
FOR EACH ROW
BEGIN
    IF NOT (NEW.column1 <=> OLD.column1 AND NEW.column2 <=> OLD.column2) THEN
        INSERT INTO change_log_table (column1, column2, changed_at) 
        VALUES (NEW.column1, NEW.column2, NOW());
        -- All changes are logged. Big Brother is watching!
    END IF;
END;


Managing complex conditions

When simple column comparisons don't cut it, and you're dealing with numerous data types and potential NULL values, it's time to lace up your trigger actions with additional complexity. Craft your trigger logic to wisely account for these diverse factors.

When triggers run amok

Ensure triggers aren't unduly popping up on non-significant updates like a change to the same value or an update from or to NULL. Construct your trigger to turn into action mode strictly for real data changes.

Here's a case for guarding against an unwanted trigger action:

CREATE TRIGGER update_trigger
AFTER UPDATE ON target_table
FOR EACH ROW
BEGIN
    IF NOT (
        (NEW.column1 <=> OLD.column1 OR (NEW.column1 IS NULL AND OLD.column1 IS NULL)) AND
        (NEW.column2 <=> OLD.column2 OR (NEW.column2 IS NULL AND OLD.column2 IS NULL))
    ) THEN
        -- Feels like Déjà vu? It's not!
    END IF;
END;


The role of DELIMITER

For defining complex trigger bodies, use DELIMITER to allow multiple statements within your trigger body without prematurely ending the CREATE TRIGGER statement.

Anusage example:

DELIMITER //

CREATE TRIGGER update_trigger
AFTER UPDATE ON target_table
FOR EACH ROW
BEGIN
    -- This is where the magic happens
END//

DELIMITER ;
