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.
SessionDay
? You can easily get it through theSessionDate
withselect to_char(SessionDate, 'Day') from sessions;
. – Marco May 11 '16 at 6:53DATE
(orTIMESTAMP
) column? – a_horse_with_no_name May 11 '16 at 6:53SessionDay
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