我在 PostgreSQL 中有以下架構
CREATE TABLE exam (
exam_id INT,
exam_name TEXT,
total_marks INT,
PRIMARY KEY(exam_id)
);
CREATE TABLE studentmarks (
studentmarks_id INT,
student_id INT,
exam_id INT,
marks_scored INT,
PRIMARY KEY(studentmarks_id),
FOREIGN KEY(exam_id) REFERENCES exam ON DELETE SET NULL,
);
我怎樣才能強制執行約束，studentmarks.marks_scored <= exam.total_marks使行為就像CHECK約束一樣？
uj5u.com熱心網友回復：
使用trigger.
您需要先創建trigger函式。
-- FUNCTION: public.check_marks_calculation()
-- DROP FUNCTION public.check_marks_calculation();
CREATE FUNCTION public.check_marks_calculation()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
_exam_id integer;
_total_marks integer;
BEGIN
IF (TG_OP = 'INSERT') THEN
_exam_id = NEW.exam_id;
_total_marks = (select total_marks from exam where exam_id=_exam_id);
if(NEW.marks_scored <= _total_marks) then
RETURN NEW;
else
raise exception 'Student Marks greater than exam Total Marks.';
end if;
end if;
END;
$BODY$;
ALTER FUNCTION public.check_marks_calculation()
OWNER TO postgres;
然后創建trigger.
CREATE TRIGGER check_toal_marks
BEFORE INSERT
ON public.studentmarks
FOR EACH ROW
EXECUTE FUNCTION public.check_marks_calculation();
注意我已經測驗過postgres
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/415501.html
標籤：
上一篇：在docker-compose中設定postgres和應用程式服務后，無法訪問Postgres
下一篇：計算兩個時間戳之間的總差值