So I'm trying to create a trigger that alters a records timestamp, I have this so far
create or replace TRIGGER job_date_set
AFTER INSERT OR UPDATE OF start_date, closing_date ON jobs
FOR EACH ROW
BEGIN
IF UPDATING THEN
CASE
WHEN :OLD.closing_date != :NEW.closing_date THEN
UPDATE jobs
SET closing_date = to_date(to_date(:NEW.closing_date,'DD/MON/YYYY')||' 23:59:59','DD/MON/YYYY HH24:MI:SS')
WHERE :OLD.job_id = job_id;
WHEN :OLD.start_date != :NEW.start_date THEN
UPDATE jobs
SET start_date = to_date(to_date(:NEW.start_date,'DD/MON/YYYY') ||' 00:00:00','DD/MON/YYYY HH24:MI:SS')
WHERE :OLD.job_id = job_id;
END CASE;
END IF;
IF INSERTING THEN
UPDATE jobs
SET closing_date = to_date(to_date(:NEW.closing_date,'DD/MON/YYYY') ||' 23:59:59','DD/MON/YYYY HH24:MI:SS')
SET start_date = to_date(to_date(:NEW.start_date,'DD/MON/YYYY')||' 00:00:00','DD/MON/YYYY HH24:MI:SS')
WHERE :OLD.job_id = job_id;
END IF;
END;
Here is the description of the errors: Compilation failed, line 17 (16:54:27) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers. PL/SQL: ORA-00933: SQL command not properly endedCompilation failed, line 15 (16:54:27) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers. PL/SQL: SQL Statement ignored
By the sounds of it, it doesn't think my if statement has been closed properly, but I have no idea where I've gone wrong
Instead of:
UPDATE jobs
SET closing_date = to_date(to_date(:NEW.closing_date,'DD/MON/YYYY') ||' 23:59:59','DD/MON/YYYY HH24:MI:SS')
SET start_date = to_date(to_date(:NEW.start_date,'DD/MON/YYYY')||' 00:00:00','DD/MON/YYYY HH24:MI:SS')
WHERE :OLD.job_id = job_id;
it's:
UPDATE jobs
SET closing_date = to_date(to_date(:NEW.closing_date,'DD/MON/YYYY') ||' 23:59:59','DD/MON/YYYY HH24:MI:SS'),
start_date = to_date(to_date(:NEW.start_date,'DD/MON/YYYY')||' 00:00:00','DD/MON/YYYY HH24:MI:SS')
WHERE :OLD.job_id = job_id;
I.e. there is a comma instead of the second SET keyword.