Video
SQL Output from Video
SQL> /**************************************************************************************************************************
SQL>
SQL> Enforcing Complex Constraints using Triggers
SQL>
SQL>
SQL> The context for this example is a small family counseling practice. The practice needs to keep track of the schedule of
SQL>
SQL> appointments for counseling sessions. Each session has one client and one counselor. It is scheduled for a specific time
SQL>
SQL> and has a duration in minutes.
SQL>
SQL> **************************************************************************************************************************/
SQL>
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
------------------------------ -----------
APPOINTMENT TABLE
CLIENT TABLE
SQL> select * from client;
CLIENT_ID FIRST_NAME LAST_NAME PHONE
---------- -------------------- -------------------- --------------
100 Ashley Lloyd (508) 759-1317
101 Sandra Welch (508) 786-0586
102 Clive Sanford (508) 297-2070
103 Abby Harris
104 Gene Mann (508) 478-4373
105 Jennifer Marin (508) 867-5309
6 rows selected.
SQL> select * from appointment;
APPT_ID COUNSELOR CLIENT_ID ROOM START_TIME DURATION
---------- -------------------- ---------- ---------- -------------------- ----------
1 Quinn Fowler 100 1 15-FEB-2016 01:00 PM 55
2 Quinn Fowler 101 1 15-FEB-2016 02:00 PM 85
3 Quinn Fowler 102 1 15-FEB-2016 03:30 PM 55
4 Elizabeth Baker 105 2 15-FEB-2016 02:00 PM 115
5 Elizabeth Baker 103 2 15-FEB-2016 11:00 AM 45
SQL> /**************************************************************************************************************************
SQL>
SQL> Gene Mann (Client # 104) would like to schedule a counseling session with the
SQL> following characteristics:
SQL>
SQL> Date: February 15, 2016
SQL> Time: 1:30 p.m.
SQL> Duration: 55 minutes
SQL> Counselor: Elizabeth Baker
SQL>
SQL> Is this appointment available?
SQL>
SQL> **************************************************************************************************************************/
SQL>
SQL> SELECT * FROM appointment
2 WHERE start_time
3 BETWEEN TO_DATE('2016-2-15 1:30 pm','yyyy-mm-dd hh:mi am')
4 AND TO_DATE('2016-2-15 1:30 pm','yyyy-mm-dd hh:mi am')+55*(1/24/60);
APPT_ID COUNSELOR CLIENT_ID ROOM START_TIME DURATION
---------- -------------------- ---------- ---------- -------------------- ----------
2 Quinn Fowler 101 1 15-FEB-2016 02:00 PM 85
4 Elizabeth Baker 105 2 15-FEB-2016 02:00 PM 115
SQL> SELECT * FROM appointment
2 WHERE start_time
3 BETWEEN TO_DATE('2016-2-15 1:30 pm','yyyy-mm-dd hh:mi am')
4 AND TO_DATE('2016-2-15 1:30 pm','yyyy-mm-dd hh:mi am')+55*(1/24/60)
5 OR start_time + duration*(1/24/60)
6 BETWEEN TO_DATE('2016-2-15 1:30 pm','yyyy-mm-dd hh:mi am')
7 AND TO_DATE('2016-2-15 1:30 pm','yyyy-mm-dd hh:mi am')+55*(1/24/60);
APPT_ID COUNSELOR CLIENT_ID ROOM START_TIME DURATION
---------- -------------------- ---------- ---------- -------------------- ----------
1 Quinn Fowler 100 1 15-FEB-2016 01:00 PM 55
2 Quinn Fowler 101 1 15-FEB-2016 02:00 PM 85
4 Elizabeth Baker 105 2 15-FEB-2016 02:00 PM 115
SQL> SELECT * FROM appointment
2 WHERE (start_time
3 BETWEEN TO_DATE('2016-2-15 1:30 pm','yyyy-mm-dd hh:mi am')
4 AND TO_DATE('2016-2-15 1:30 pm','yyyy-mm-dd hh:mi am')+55*(1/24/60)
5 OR start_time + duration*(1/24/60)
6 BETWEEN TO_DATE('2016-2-15 1:30 pm','yyyy-mm-dd hh:mi am')
7 AND TO_DATE('2016-2-15 1:30 pm','yyyy-mm-dd hh:mi am')+55*(1/24/60))
8 AND
9 counselor = 'Elizabeth Baker';
APPT_ID COUNSELOR CLIENT_ID ROOM START_TIME DURATION
---------- -------------------- ---------- ---------- -------------------- ----------
4 Elizabeth Baker 105 2 15-FEB-2016 02:00 PM 115
SQL> SELECT count(*) FROM appointment
2 WHERE (start_time
3 BETWEEN TO_DATE('2016-2-15 1:30 pm','yyyy-mm-dd hh:mi am')
4 AND TO_DATE('2016-2-15 1:30 pm','yyyy-mm-dd hh:mi am')+55*(1/24/60)
5 OR start_time + duration*(1/24/60)
6 BETWEEN TO_DATE('2016-2-15 1:30 pm','yyyy-mm-dd hh:mi am')
7 AND TO_DATE('2016-2-15 1:30 pm','yyyy-mm-dd hh:mi am')+55*(1/24/60))
8 AND
9 counselor = 'Elizabeth Baker';
COUNT(*)
----------
1
SQL> /**************************************************************************************************************************
SQL>
SQL> Create a Trigger to do the following:
SQL>
SQL> 1. Whenever someone tries to insert a record
SQL> 2. Run a query to count the number of conflicts
SQL> 3. If the number of conflicts is more than 0
SQL> - prevent the record from being inserted
SQL> - report to the user (or application) why
SQL>
SQL> **************************************************************************************************************************/
SQL>
SQL> CREATE OR REPLACE TRIGGER schedule_conflict
2 BEFORE INSERT ON appointment
3 FOR EACH ROW
4 DECLARE
5 v_conflicts int;
6 BEGIN
7 SELECT count(*) into v_conflicts from appointment
8 WHERE(
9 start_time
10 BETWEEN :new.start_time
11 AND :new.start_time + :new.duration*(1/24/60)
12 OR
13 start_time + duration*(1/24/60)
14 BETWEEN :new.start_time
15 AND :new.start_time + :new.duration*(1/24/60)
16 ) AND counselor = :new.counselor;
17 IF v_conflicts >0 THEN
18 RAISE_APPLICATION_ERROR(-20001, 'Cannot Add appointment. There is a conflcting appointment for ' || :new.counselor);
19 END IF;
20 END schedule_conflict;
21 /
Trigger created.
SQL> insert into appointment values(6,'Elizabeth Baker',104,2, to_date('2016-2-15 1:30 pm','yyyy-mm-dd hh:mi am'),55);
insert into appointment values(6,'Elizabeth Baker',104,2, to_date('2016-2-15 1:30 pm','yyyy-mm-dd hh:mi am'),55)
*
ERROR at line 1:
ORA-20001: Cannot Add appointment. There is a conflcting appointment for Elizabeth Baker
ORA-06512: at "GOVE.SCHEDULE_CONFLICT", line 15
ORA-04088: error during execution of trigger 'GOVE.SCHEDULE_CONFLICT'
SQL> SELECT * FROM appointment;
APPT_ID COUNSELOR CLIENT_ID ROOM START_TIME DURATION
---------- -------------------- ---------- ---------- -------------------- ----------
1 Quinn Fowler 100 1 15-FEB-2016 01:00 PM 55
2 Quinn Fowler 101 1 15-FEB-2016 02:00 PM 85
3 Quinn Fowler 102 1 15-FEB-2016 03:30 PM 55
4 Elizabeth Baker 105 2 15-FEB-2016 02:00 PM 115
5 Elizabeth Baker 103 2 15-FEB-2016 11:00 AM 45