SQLite Forum

MySQL Trigger Code convert to work in SQLite?
Login

MySQL Trigger Code convert to work in SQLite?

(1) By kimWoo2 (kimmierak2) on 2021-11-04 03:50:50 [link] [source]

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'
FOR EACH ROW BEGIN
CASE check_grade
WHEN NEW.GRADE > 12 THEN SET NEW.GRADE = OLD.GRADE;
WHEN NEW.GRADE < 9 THEN SET NEW.GRADE = OLD.GRADE;
END AS checked_grade,
CASE new_grade
WHEN (SELECT COUNT(ID) FROM highschooler WHERE name = NEW.name and grade = NEW.grade) = 1 THEN SET NEW.GRADE = OLD.GRADE; 
END AS newed_grade;
END;

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

Any help or advice is highly appreciate it!

(2.1) By ddevienne on 2021-11-04 07:47:08 edited from 2.0 in reply to 1 [source]

SQLite has no procedural dialect to use in triggers, so you cannot do IF tests
and plain SET assignments like you show. You only have access to plain SQL statements
(probably both DDL and DML ones), as usual, like at the command line.

So your SETs must be UPDATEs, for example.

To emulate your boolean tests, either move them inside of the UPDATE statement,
or also look at the WHEN clause of triggers.

If a WHEN clause is supplied, the SQL statements specified are only executed if the WHEN clause is true.
If no WHEN clause is supplied, the SQL statements are executed every time the trigger fires.

It may force you to create several triggers with different non-intersecting WHEN clauses
to achieve something similar to your MySQL trigger.