mysql - What is wrong with the following trigger before an update?
问题描述
I have to implement the following trigger in mysql before an update is made on the enrollment table
Enforce the constraint that all letter grades must be one ofA, B, C, D (no +/-) in this database). Also ensure that the grade point value matches: A=4.0, B=3.0, C=2.0, D=1.0
CREATE TABLE enrollment (
student_id CHAR(4) not null,
student_name VARCHAR(100) not null,
course_id CHAR(7) not null,
enroll_date DATE not null,
letter_grade CHAR(2),
grade_points DECIMAL(2,1),
PRIMARY KEY (student_id, course_id)
);
This is what I have so far:
create trigger letter_grade_checker before update on enrollment
for each row
begin
if ((new.letter_grade != 'A' and new.grade_points != 4.0)
or (new.letter_grade != 'B' and new.grade_points != 3.0)
or (new.letter_grade != 'C' and new.grade_points != 2.0)
or (new.letter_grade != 'D' and new.grade_points != 1.0))
then SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid grade or grade_points provided';
end if;
end;
Although it's not working for the following update statement:
UPDATE enrollment SET letter_grade = 'B', grade_points = 3.0
WHERE student_id = '9999' and course_id = 'CSC-399';
解决方案
你的布尔表达式应该是这样的
if (new.letter_grade != 'A' or new.grade_points != 4.0)
and (new.letter_grade != 'B' or new.grade_points != 3.0)
and (new.letter_grade != 'C' or new.grade_points != 2.0)
and (new.letter_grade != 'D' or new.grade_points != 1.0)
then ...
因为逻辑是
!((a AND b) OR (c AND d)) = !(a AND b) AND !(c AND d) = (!a OR !b) AND (!c OR !d)
推荐阅读
- javascript - 单击按钮后,网页会自行刷新。我怎么能避免呢?
- python - 我正在处理 django 中的 RSS 提要,但它给了我一个错误
- python-3.x - pyqt5获取数据库中组合框选定项的ID
- xml - 如何(正确)使用“mode="copy-no-namespaces"”?
- php - 查询从数据库中删除记录,但表行不淡出那一个
- c# - MediaCapture.PrepareLowLagRecordToStorageFileAsync() 在 HoloLens 上引发“类未注册”异常
- vb.net - 在 ms 访问中以编程方式创建列到表
- reactjs - react-cookie-consent 在点击时存储数据两次
- c# - “索引超出数组边界”错误背后的原因
- mysql - MySQL undo log 不断增长