CODEWITHSUNDEEP
mysqlsqlite
kimmieRak2
kimmieRak2

Reputation: 9

SQLite - Converting MySQL trigger to work on SQLite

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!

Upvotes: 0

Views: 140

Answers (1)

forpas
forpas

Reputation: 164089

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.

Upvotes: 1

Related Questions