CODEWITHSUNDEEP
mysqltriggersmysql-workbench
Jean-Marc Guay
Jean-Marc Guay

Reputation: 1

Why does my trigger doesn't work ? Error 1064

I've a syntax error in MySql Workbench but I can't seem to find it... Is my trigger correct ?

CREATE TRIGGER after_delete_user AFTER UPDATE
ON users FOR EACH ROW
BEGIN
    IF NEW.deletionDate != NULL
        THEN
            DELETE FROM requests WHERE user_id = OLD.user_id;
            DELETE FROM appointments WHERE user_id = OLD.user_id;
    END IF;
END;

Thanks !

Upvotes: 0

Views: 27

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

Need to define Delimiter to something else other than ;. Redefine it back to ; at end. To compare null values, we need to use IS NOT NULL

DELIMITER $$

CREATE TRIGGER after_delete_user AFTER UPDATE
ON users FOR EACH ROW
BEGIN
    IF NEW.deletionDate IS NOT NULL
        THEN
            DELETE FROM requests WHERE user_id = OLD.user_id;
            DELETE FROM appointments WHERE user_id = OLD.user_id;
    END IF;
END$$ 

DELIMITER ;

Upvotes: 1

Related Questions