How can I resolve “success with compilation error” error while creating trigger in Oracle PL/SQL?

I am trying to create a trigger that updates “event” table rather than updating the “v_event” view.

CREATE OR REPLACE TRIGGER t_update_event
INSTEAD OF UPDATE ON v_event
BEGIN 
    UPDATE Event
    SET start_date = :NEW.start_date,
    end_date = :NEW.end_date,
    start_time = :NEW.start_time,
    end_time = :NEW.end_time,
    description = :NEW.description,
    WHERE event_id = :OLD.event_id
END

When I run this script I get “success with compilation error” error.

What am I doing wrong?

Answer

Pay attention to what you do.

CREATE OR REPLACE TRIGGER t_update_event
INSTEAD OF UPDATE ON v_event
BEGIN 
    UPDATE Event
    SET start_date = :NEW.start_date,
    end_date = :NEW.end_date,
    start_time = :NEW.start_time,
    end_time = :NEW.end_time,
    description = :NEW.description,          --> superfluous comma
    WHERE event_id = :OLD.event_id           --> missing semi-colon
END                                          --> missing semi-colon

In order to find out what went wrong, either

SQL> show err

immediately after you see the error (if your tool supports it; SQL*Plus does), or query user_errors:

select * from user_errors where name = 'T_UPDATE_EVENT';

---

Fixed, it looks like this:

SQL> create or replace trigger t_update_event
  2    instead of update on v_event
  3  begin
  4      update event set
  5        start_date   = :new.start_date,
  6        end_date     = :new.end_date,
  7        start_time   = :new.start_time,
  8        end_time     = :new.end_time,
  9        description  = :new.description
 10      where event_id = :old.event_id;
 11  end;
 12  /

Trigger created.

SQL>