I have written a case statement which gets the current status of an application which a user has made; this statement fires in a trigger whenever a users application status changes. When their status becomes 'Accepted' I want my students table to have a row updated to indicate they are on placement.
I know the method works as I tested it by returning 1 and 0 on a previous version, but since incorporating an UPDATE method my trigger keeps failing, how would I go about incorporating an UPDATE call on my case statement?
Case statement code:
SELECT DISTINCT CASE
WHEN get_status(:NEW.status_id) = LOWER('Applicant Accepted Offer')
THEN
UPDATE students
SET students.student_on_placement = 1
WHERE applications.student_id = :OLD.student_id;
END AS status_result
FROM status;
get_status method (too avoid questions about my method)
CREATE OR REPLACE FUNCTION get_status( this_id NUMBER )
RETURN VARCHAR2
AS this_type status.status_type%TYPE;
BEGIN
SELECT status_type
INTO this_type
FROM status
WHERE status_id = this_id;
RETURN LOWER(this_type);
END get_status;
