2

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.

  • 2
    Why do you want to store the SessionDay? You can easily get it through the SessionDate with select to_char(SessionDate, 'Day') from sessions;. – Marco May 11 '16 at 6:53
  • 1
    Why don't you store sessiondate & sessiontime in a single DATE (or TIMESTAMP) column? – a_horse_with_no_name May 11 '16 at 6:53
  • I was doing another particular exercises with time and date separately as well as day. I found it easier to have it like this. – Jakub A Suplicki May 11 '16 at 7:16
  • With the SessionDay in a separate field you need to verify if this day is the same as for the date. Makes is more complicate instead. – Marco May 11 '16 at 7:36
1

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;
/
  • Thank you for your answer, I have found it very useful. Seems I was missing that error code in line: raise_application_error(-20001(...) and I also changed line from to_char(:new.SessionDate, 'HH24MI') to :new.SessionTime . Now it suits my needs perfectly. Thanks again. – Jakub A Suplicki May 11 '16 at 7:11
  • Changed my answer. You might consider to use one field for the time instead of the 3 that you use now. This way you avoid 'confusions' like this. The to_char and to_date functions are used for this combination. – Marco May 11 '16 at 7:34
  • Alright I deleted the SessionDay, but my trigger is not working properly. I am not sure how to call it now. Something like if to_char(:new.SessionDate, ' Day') = 'Sunday' or how can I specify that it is about the day and it is supposed to know just from the dd/month/yyyy which of the day of the week it is? – Jakub A Suplicki May 12 '16 at 5:29
  • You should use it as trim(to_char(:new.SessionDate, ' Day')) because the result of the to_char is a string of 9 characters. Sunday has 6 so 3 spaces are added which makes that the result is not equal to 'Sunday' but to 'Sunday '. You can also use 'D' instead of 'Day' to get the number of the day of the week. Sunday = 1 and Saturday = 6. – Marco May 12 '16 at 8:42
  • Amazing, trim solved my problem. Thank you once again. – Jakub A Suplicki May 13 '16 at 0:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.