CODEWITHSUNDEEP
mysqltriggers
cva
cva

Reputation: 99

Use Case statement inside if else block in mysql triggers

I am new to the mysql triggers. Let assume if i have a table name teachers contains 

teacher_id,
teacher_name,
description,
student_id,
class,
student_year

I want to create a AFTER INSERT and AFTER UPDATE triggers on teachers table. for that i have a table name called teacherslogs with id,teacher_id,student_year,class,student_id columns

so whenever record insert into teachers table it should insert into teacherslogs.(for this trigger able to write)

But for after update trigger facing issue.

Requirement:

whenever student_id changed in teachers table we need to insert a record into teacherslogs with along another columns changes in the teachers table for that record for this i have written like this

  IF ((NEW.student_id <=> OLD.student_id) = 0) Then
    select student_id,class,student_year into student_id_,class_,year_ from `employees`.`teachers` where teacher_id = OLD.teacher_id;
    Insert into `employees`.`teacherlogs`(teacher_id,student_year,class,student_id) values (OLD.teacher_id,year_,class_,student_id_);
    END IF;

But whenever changes in other columns like class,student_year we need to update the teacherslogs table for that record where teacher_id and student_id

Final Trigger Query

create trigger `updateteacherlogs`
AFTER UPDATE ON `employees`.`teacher` FOR EACH ROW 
BEGIN
    DECLARE teacher_id_ int(11);
    Declare student_id_ int(11);
    Declare class_ varchar(150);
    Declare year_ int(11);
    Declare dummy tinyint;

    IF ((NEW.student_id <=> OLD.student_id) = 0) Then
    select student_id,class,student_year into student_id_,class_,year_ from `employees`.`teachers` where teacher_id = OLD.teacher_id;
    Insert into `employees`.`teacherlogs`(teacher_id,student_year,class,student_id) values (OLD.teacher_id,year_,class_,student_id_);
    ELSE
         CASE WHEN (NEW.student_year <=> OLD.student_year) = 0
         THEN update `teacherlogs` set student_year = NEW.student_year where student_id=OLD.student_id and teacher_id = OLD.teacher_id;
         WHEN (NEW.class <=> OLD.class) = 0
         THEN update `teacherlogs` set class = NEW.class where student_id=OLD.student_id and teacher_id = OLD.teacher_id
         ELSE select @dummy  END 

    end if;
END

But i am getting an exception at end if.

is that correct way to solve my problem or any other ways can we do?

Upvotes: 0

Views: 1353

Answers (1)

Nick
Nick

Reputation: 147146

The reason for your syntax error is that your CASE statement needs to end in END CASE;, not just END.

I don't think that the CASE statement actually works for your requirements though as if NEW.student_year <=> OLD.student_year then NEW.class will not get compared against OLD.class to see if that value also needs to be updated. That is because (from the manual):

For the second syntax, each WHEN clause search_condition expression is evaluated until one is true, at which point its corresponding THEN clause statement_list executes

You can get around that by just replacing the ELSE CASE ... with an ELSEIF clause:

ELSEIF NEW.student_year <=> OLD.student_year OR NEW.class <=> OLD.class THEN
UPDATE teacherlogs
  SET student_year = NEW.student_year, class = NEW.class 
  WHERE student_id = OLD.student_id AND teacher_id = OLD.teacher_id;

This should work because (based on my understanding from your question of how your database is configured) the value of student_year and class in teacherlogs should be the same as OLD.student_year and OLD.class and if they are the same as NEW.student_year and NEW.class the values won't get updated.

If my understanding is incorrect, you will need modify the update to have conditions on each value:

ELSE
    UPDATE teacherlogs
    SET student_year = IF(NEW.student_year <=> OLD.student_year, NEW.student_year, student_year),
    class = IF(NEW.class <=> OLD.class, NEW.class, class)
    -- repeat for all columns
    WHERE student_id = OLD.student_id AND teacher_id = OLD.teacher_id;
END IF;

Upvotes: 1

Related Questions