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
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.
SETs must be
UPDATEs, for example.
To emulate your boolean tests, either move them inside of the
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
to achieve something similar to your MySQL trigger.