Oracle Trigger – Before Any UPDATE, DELETE, or INSERT

oracleoracle-sql-developertrigger

I am trying to create a database trigger in Oracle SQL Developer that would fire before any UPDATE, DELETE, or INSERT statement occurs on the sessions table.

My sessions table:

CREATE TABLE sessions (
SessionNo NUMBER (2,0),
SessionTime VARCHAR2 (5),
SessionDay VARCHAR2 (20),
SessionDate DATE,
SpecID VARCHAR2 (2),
RoomNo CHAR (3),
BuildingNo CHAR (2),
FormID CHAR (4));

My example insert statement:

INSERT INTO sessions VALUES (1, '9', 'Monday', TO_DATE ('02/06/2016', 'mm/dd/yyyy'),
                        12, 108, 'M', 0050);

This trigger is supposed to check the date and time attributes, and if the day is a Saturday or a Sunday, or if the time is outside the school business hours (9am to 3pm), a message:

Out of business hours – this transaction has not completed, please change the
date/time

…should be displayed.

Best Answer

I do not think that the check is needed on delete. So create a trigger like:

CREATE OR REPLACE TRIGGER check_session_timing
  BEFORE INSERT OR UPDATE ON sessions
  FOR EACH ROW
BEGIN
    if   :new.SessionDay = 'Saturday'
      or :new.SessionDay = 'Sunday' then
      raise_application_error(-20001, 'Out of business hours – this transaction has not completed, please change the date/time');
    end if;
    if :new.SessionTime not between 900 and 1500 then
      raise_application_error(-20001, 'Out of business hours – this transaction has not completed, please change the date/time');
    end if;
END;
/
Related Question