SQLite - Converting MySQL trigger to work on SQLite

Question

I just had starting taking classes for SQLite and one of my assignment wants me to create a trigger for an SQLite database. I know that certain functions from MySQL doesn't work on SQLite like the IF statement. Can anyone help me convert this MySQL trigger code to work on SQLite?

This is the trigger code in MySQL: (This code has been tested and works with the same database in MySQL)

CREATE TRIGGER `grade_update_check` 
BEFORE UPDATE ON `highschooler`
 FOR EACH ROW BEGIN
IF (NEW.GRADE > 12) 
THEN SET NEW.GRADE = OLD.GRADE;
 END IF;
 IF (NEW.GRADE < 9) 
THEN SET NEW.GRADE = OLD.GRADE;
 END IF; 
IF (SELECT COUNT(ID) FROM highschooler WHERE name = NEW.name and grade = NEW.grade) = 1 
THEN SET NEW.GRADE = OLD.GRADE; 
END IF; 
END

This my converted SQLite code for the same trigger:

CREATE TRIGGER 'grade_update_check' BEFORE UPDATE ON 'Highschooler' 
BEGIN
SELECT CASE
WHEN (SELECT grade FROM Highschooler WHERE NEW.GRADE > 12) THEN SET NEW.GRADE = OLD.GRADE;
WHEN (SELECT grade FROM Highschooler WHERE NEW.GRADE < 9) THEN SET NEW.GRADE = OLD.GRADE;
WHEN ((SELECT COUNT(ID) FROM highschooler WHERE name = NEW.name and grade = NEW.grade) = 1) THEN SET NEW.GRADE = OLD.GRADE;
END;
END;

It throws an error of Error: near "SET": syntax error when I try to execute it.

Any help or advice is highly appreciate it!

forpas · Accepted Answer · 2021-11-04 13:25:07Z

1

What you want from the trigger is to abort the update operation if any of the 3 conditions is met.

This can be done in SQLite like this:

CREATE TRIGGER grade_update_check BEFORE UPDATE ON Highschooler 
BEGIN
  SELECT 
    CASE
      WHEN NEW.GRADE < 9 OR NEW.GRADE > 12
        OR ((SELECT COUNT(ID) FROM highschooler WHERE name = NEW.name and grade = NEW.grade) = 1) 
      THEN RAISE(ABORT, 'your message here')
    END;
END;

The function RAISE(ABORT, ...) will abort the operation and the row will not be updated.
You can customize the message that you will get, or leave it empty.

answered Nov 4, 2021 at 13:25

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kimmieRak2 Over a year ago

How do you implement the SET NEW.GRADE = OLD.GRADE ?

forpas Over a year ago

@kimmieRak2 I don't. What SET NEW.GRADE = OLD.GRADE does is keep the current value and not update the column. This is done by RAISE(ABORT, ..) which aborts the update.

Collectives™ on Stack Overflow

SQLite - Converting MySQL trigger to work on SQLite

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related