Zachary Zachary - 1 month ago 14
SQL Question

SQL on ORACLE APEX 5.x

I am attempting to update the time on a table called application_history, each of the column names are listed in the 'CASE' section where I attempt to set the 'status' to the column name. I then later try to use that column to try and insert the time in that column.
Here is what I have attempted, bearing in mind that I have no idea what triggers limitations are.

create or replace TRIGGER update_application_history AFTER UPDATE ON application
FOR EACH ROW
DECLARE
status VARCHAR(35);
BEGIN
CASE
WHEN old.application_status LIKE 'Application Withdrawn' THEN SET status = 'APPLICATION_WITHDRAWN'
WHEN old.application_status LIKE 'Applicant Accepted Offer' THEN SET status = 'APPLICANT_ACCEPTED_OFFER'
WHEN old.application_status LIKE 'Applicant Declined Offer' THEN SET status = 'APPLICANT_DECLINED_OFFER'
WHEN old.application_status LIKE 'Applicant Invited for Assessment' THEN SET status = 'INVITED_TO_ASSESMENT_CENTRE'
WHEN old.application_status LIKE 'Applicant Invited for Interview' THEN SET status = 'INVITED_FOR_INTERVIEW'
WHEN old.application_status LIKE 'Applicant Offered Position' THEN SET status = 'APPLICANT_OFFERED_POSITION'
WHEN old.application_status LIKE 'Applicant Rejected'THEN SET status = 'APPLICANT_REJECTED'
END
UPDATE application_history
SET status = TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
WHERE old.arn LIKE arn
END;

Answer
  1. You can not use dynamic column names in PL/SQL (at least not like that)
  2. Assignment of values to variables is handled with the := operator in PL/SQL.
  3. Don't use LIKE when you really should use =
  4. Why do you store sysdate as a varchar2 in the application_history table? Maybe those columns are indeed of type varchar2, but if they only store dates then please consider changing that to date.

All in all, your trigger should look something like this:

create or replace TRIGGER update_application_history AFTER UPDATE ON application
FOR EACH ROW
BEGIN 
  CASE
    WHEN :old.application_status = 'Application Withdrawn' 
    THEN 
      UPDATE application_history
      SET APPLICATION_WITHDRAWN = sysdate
      WHERE :old.arn = arn;
    WHEN :old.application_status = 'Applicant Accepted Offer' 
    THEN 
      UPDATE application_history
      SET APPLICANT_ACCEPTED_OFFER = sysdate
      WHERE :old.arn = arn;
    -- you get the point... add the rest here
    END CASE;
END;
Comments