Oracle SQL Trigger error: ORA-00933

Question

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

It looks like jobs.closing_date and jobs.start_date are date columns. If so, your to_date(to_date( should be to_date(to_char( - you want to convert the date into a character string in DD/MON/YYYY format in order to append ' 23:59:59' to it and convert it back to a date. (It might be simpler to trunc it, add 1 and subtract interval '1' second.) — William Robertson
– William Robertson, Commented Oct 31, 2016 at 11:49

Codo · Accepted Answer · 2016-10-30 17:04:58Z

1

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.

answered Oct 30, 2016 at 17:04

Codo

79.7k18 gold badges176 silver badges218 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2016-10-30 17:04:19Z

1

When you are modifying the table the trigger is defined on, you want a before update trigger. So, something like this:

create or replace TRIGGER job_date_set
  BEFORE 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 
        SELECT to_date(to_date(:NEW.closing_date,'DD/MON/YYYY')||' 23:59:59','DD/MON/YYYY HH24:MI:SS')
        INTO :NEW.closing_date
        FROM dual;
      WHEN :OLD.start_date <> :NEW.start_date THEN 
        SELECT to_date(to_date(:NEW.start_date,'DD/MON/YYYY') ||' 00:00:00','DD/MON/YYYY HH24:MI:SS')
        INTO :NEW.start_date
        FROM dual;
    END CASE;
  END IF;
  IF INSERTING THEN
    SELECT to_date(to_date(:NEW.closing_date,'DD/MON/YYYY') ||' 23:59:59','DD/MON/YYYY HH24:MI:SS'),
           to_date(to_date(:NEW.start_date,'DD/MON/YYYY')||' 00:00:00','DD/MON/YYYY HH24:MI:SS')
    INTO :NEW.closing_date, :NEW.start_date;
  END IF;
END;

answered Oct 30, 2016 at 17:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Zachary Over a year ago

I think My plan was to have a trigger before insert that inserts an auto increment, so I would have to edit the date after, do you think It would be better to incorporate this code into that trigger or have two separate triggers doing two different tasks?

Collectives™ on Stack Overflow

Oracle SQL Trigger error: ORA-00933

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related