CREATE OR REPLACE FUNCTION update_user_grade()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = ‘INSERT’ THEN
IF EXISTS (
SELECT 1
FROM courses c
WHERE c.user_id = NEW.user_id
AND c.subject = NEW.subject
AND c.course_code = NEW.course_code
AND c.credits = NEW.credits) then
IF (SELECT c.grade FROM courses c WHERE c.user_id = NEW.user_id AND c.subject = NEW.subject AND c.course_code = NEW.course_code
AND c.credits = NEW.credits) < NEW.grade THEN
UPDATE users
SET grade = grade + (NEW.grade * NEW.credits) – (SELECT c.grade * c.credits FROM courses c
WHERE c.user_id = NEW.user_id AND c.subject = NEW.subject AND c.course_code = NEW.course_code AND c.credits = NEW.credits AND c.grade < NEW.grade)
WHERE id = NEW.user_id;
ELSE
RETURN NULL;
END IF;
ELSE
IF NEW.graded IS TRUE AND NEW.grade IS NOT NULL THEN
UPDATE users
SET grade = COALESCE(grade + (NEW.grade * new.credits),0),
credits = COALESCE(credits + new.credits, 0),
counted_credits = COALESCE(counted_credits + new.credits, 0)
WHERE id = NEW.user_id;
ELSIF NEW.graded IS FALSE AND (NEW.grade = 1 OR NEW.grade = 0) THEN
UPDATE users
SET credits = COALESCE(credits + new.credits, 0)
WHERE id = NEW.user_id;
END IF;
END IF;
END IF;
RETURN NEW;