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
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'
SET status = TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
WHERE old.arn LIKE arn
:=operator in PL/SQL.
LIKEwhen you really should use
varchar2in the application_history table? Maybe those columns are indeed of type
varchar2, but if they only store dates then please consider changing that to
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;