Trigger with multipe IF conditions doesn't trigger I am trying to update the table latest_activities with the latest activity a employee had.

An activity has multiple conditions to be considered as the latest activity.

The reason why I don't do a JOIN to get the latest activity from the table activity is because of performance and a sub-optimal database design I cannot change.

This is the trigger I have made. It doesn't add any records to latest_activities. I suspect the problem is that there are a difference on IF() and IF. Also I am not sure that my REPLACE INTO is correct

DELIMITER $$
CREATE TRIGGER update_latest_activity
    AFTER INSERT ON activity
    FOR EACH ROW
BEGIN
    IF NEW.log = 0
        AND (NEW.type = 'hired'
        OR (NEW.type = 'note'
        AND JSON_VALID(NEW.data) = 1
        AND NEW.data-&gt;&gt;'$.status' = 1))
    THEN
        REPLACE INTO
            latest_activities (employee_id, activity_id)
        SELECT
            pivot.employee_id AS employee_id,
            pivot.activity_id AS activity_id
        FROM
            pivot
        WHERE
            pivot.activity_id = NEW.id
            AND pivot.type LIKE '%Employee';
    END IF;
END$$
DELIMITER ;


UPDATE

After debugging (thanks to @akina) I found that NEW.id for some reason isn't working when used in the REPLACE INTO statement pivot.activity_id = NEW.id. Hard-coding the value will work, so the query is correct.

PROBLEM SOLVED

The problem was obvious when I took a step back. Trigger should be on the pivot table because the relationship is first created there AFTER the data has been inserted into the activity table facepalm.
Debug your trigger.

Create service table.

CREATE TABLE service (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                      step INT,
                      data TEXT,
                      dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP);


Add tracing info save (example code).

CREATE TRIGGER update_latest_activity
    AFTER INSERT ON activity
    FOR EACH ROW
BEGIN
    INSERT INTO service (step, data) VALUES (1, NEW.log);
    INSERT INTO service (step, data) VALUES (2, NEW.log = 0);
    INSERT INTO service (step, data) VALUES (3, NEW.type);
    INSERT INTO service (step, data) VALUES (4, NEW.data);
    INSERT INTO service (step, data) VALUES (5, JSON_VALID(NEW.data));
    INSERT INTO service (step, data) VALUES (6, NEW.data-&gt;&gt;'$.status');
    INSERT INTO service (step, data) VALUES (7, NEW.log = 0
                                                AND (NEW.type = 'hired'
                                                OR (NEW.type = 'note'
                                                    AND JSON_VALID(NEW.data) = 1
                                                    AND NEW.data-&gt;&gt;'$.status' = 1)));
    IF NEW.log = 0
        AND (NEW.type = 'hired'
        OR (NEW.type = 'note'
        AND JSON_VALID(NEW.data) = 1
        AND NEW.data-&gt;&gt;'$.status' = 1))
    THEN
        INSERT INTO service (step, data) VALUES (8, 'Execute THEN branch');
        REPLACE INTO
            latest_activities (employee_id, activity_id)
        SELECT
            pivot.employee_id AS employee_id,
            pivot.activity_id AS activity_id
        FROM
            pivot
        WHERE
            pivot.activity_id = NEW.id
            AND pivot.type LIKE '%Employee';
    END IF;
END


Insert record which must (as you think) to perform the action.

Analyse the info in service table.