Zachary Zachary - 1 month ago 6
SQL Question

Oracle SQL Trigger error: ORA-00933

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

Answer

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.

Comments