CODEWITHSUNDEEP
sqlitetriggers
Lorenzo Rigamonti
Lorenzo Rigamonti

Reputation: 1775

Multiple update trigger on SQLLite

What I want to do is to manage the grade attribution of new Students. If the inserted tuple containing Student ID and grade has a grade value less than 9 or greater than 12, we want to change the value to NULL. At the same time, if the inserted tuple having a grade null value, we want to change it to 9.

What is wrong with the following code?

CREATE TRIGGER R1
AFTER INSERT ON Students
FOR EACH ROW 
BEGIN

  UPDATE Students
     SET grade = NULL 
   WHERE grade < 9 OR grade > 12;

  UPDATE Students
     SET grade = 9 
   WHERE grade = NULL;

END;

Note that the two UPDATE statements have to be performed on the Student table just after the INSERT is executed. We will have null values to be updated to grade 9 and grade > 9 or > 12 to be updated to NULL

Upvotes: 1

Views: 4253

Answers (2)

Aris
Aris

Reputation: 5057

Another way to do this is to move the condition in the where clause:

CREATE TRIGGER tr1 AFTER INSERT ON Student
BEGIN
     UPDATE Student SET grade = null WHERE id = NEW.id and (new.grade<9 or new.grade>12);
     UPDATE Student SET grade = 9 WHERE id = NEW.id and new.grade is null;
END;

Upvotes: 0

biziclop
biziclop

Reputation: 14596

You should replace the second condition from = NULL to IS NULL:

UPDATE Students
     SET grade = 9 
   WHERE grade IS NULL;

Proof with = NULL and IS NULL: http://sqlfiddle.com/#!5/a51de/1

Other solution:

http://sqlfiddle.com/#!5/78288/1

CREATE TRIGGER TStudents3
AFTER INSERT ON Students3
FOR EACH ROW
/* we check update conditions here */
WHEN  NEW.grade IS NULL
  OR  NEW.grade < 9
  OR  NEW.grade > 12
BEGIN

  UPDATE Students3
     SET grade =
       CASE
         WHEN grade IS NULL THEN 9
         /* actually you could drop this 2 lines, because a missing ELSE returns NULL: */
         WHEN grade < 9 OR grade > 12 THEN NULL
         ELSE grade
       END
   /* We just want to update the freshly inserted row, not all row! */
   WHERE  id = NEW.id;

END

Upvotes: 2

Related Questions