CODEWITHSUNDEEP
mysqltriggers
Joe Kennedy
Joe Kennedy

Reputation: 11

Why doesn't this trigger work the way I want it to?

I have two tables in my mysql database: Advisor: id Student: advisorID | major

I want to update Student where the deleted ID in Advisor is equal to advisorID and use a set of if statements to change that student's advisorID value based on that student's major. This is what I have thus far:

    delimiter //
    create trigger advisor_delete after delete
    on advisor
        UPDATE Student
            IF Student.major = 'major1' THEN SET Student.advisorID = 1;
            ELSEIF Student.major = 'major2' THEN SET Student.advisorID = 2;
            ELSEIF Student.major = 'major3' THEN SET Student.advisorID = 3;
            ELSE SET Student.advisorID = 4;
            ENDIF;
        WHERE Student.advisorID = OLD.id;
    end//

Any help is welcome. Thanks.

Upvotes: 1

Views: 184

Answers (1)

manji
manji

Reputation: 47978

I don't know this UPDATE/IF ELSEIF syntax, but CASE statement will work for you:

delimiter //
create trigger advisor_delete after delete
on advisor
    UPDATE Student
        SET Student.advisorID = 
            CASE Student.major WHEN 'major1' THEN 1
                               WHEN 'major2' THEN 2
                               WHEN 'major3' THEN 3
                               ELSE 4
            END
    WHERE Student.advisorID = OLD.id;
end//

Upvotes: 1

Related Questions