CODEWITHSUNDEEP
mysqltriggers
cullan
cullan

Reputation: 280

MySQL BEFORE INSERT TRIGGER not working

I have a trigger and I want to update a column called error_id on different cases. I am inserting a row into a table called data and data has a column error_id and if it passes all the cases, then it should have error_id equal to 1. This is not working for some reason.. please help

CREATE TRIGGER error_stamp 
BEFORE INSERT ON data
FOR EACH ROW 
BEGIN
    IF NEW.error_id = 0 THEN
        IF NEW.type_id = 4 AND NEW.date_released <= NEW.date THEN
            SET NEW.error_id = 3;
        ELSEIF NEW.type_id = 4 AND NEW.date_released < '1990-01-01' THEN
            SET NEW.error_id = 4;
        ELSEIF NEW.type_id = 4 AND NEW.date_released > DATE_FORMAT(SYSDATE(), '%Y-%m-%d') THEN
            SET NEWdata.error_id = 5;
        ELSEIF (NEW.type_id = 4 AND NEW.date_released IS NULL) THEN
            SET NEW.error_id = 6;
        ELSEIF (NEW.date IS NULL) THEN
            SET NEW.error_id = 7;
        ELSEIF (NEW.type_id = 5 AND NEW.date_released IS NULL) THEN
            SET NEW.error_id = 8;
        ELSEIF (NEW.value IS NULL) THEN
            SET NEW.error_id = 2;
        ELSE 
            SET NEW.error_id = 1;
        END IF;
    END IF;
    -- RETURN NEW; -- result is ignored since this is an AFTER trigger
END

Upvotes: 0

Views: 147

Answers (1)

Rahul
Rahul

Reputation: 77846

Not sure if typo but the below code block should result in compilation error

    ELSEIF NEW.type_id = 4 AND NEW.date_released > DATE_FORMAT(SYSDATE(), '%Y-%m-%d') THEN
        SET NEWdata.error_id = 5; <-- This line

Should be 

SET NEW.error_id = 5;

Upvotes: 1

Related Questions