Video
SQL Output from Video
SQL> /**************************************************************************************************************************
SQL>
SQL> Using Triggers to log the Creation and Modificaiton of Records
SQL>
SQL>
SQL> The context for this example is a small family counseling practice where we will need to keep track of information
SQL>
SQL> counselors, clients, and appointments for counseling.
SQL>
SQL>
SQL> In this example, we will schedule counseling sessions where a client will be counseled by a counselor at a specific date
SQL>
SQL> and time. We want to be sure that the client specified for the appointment is really a client of the practice.
SQL>
SQL>
SQL> **************************************************************************************************************************/
SQL>
SQL> create table appointment(
2 APPT_ID int primary key,
3 COUNSELOR varchar(20),
4 CLIENT_ID int,
5 ROOM number(1,0),
6 START_TIME date,
7 DURATION number(3,0) ,
8 create_date date,
9 created_by varchar(30),
10 change_date date,
11 changed_by varchar(30)
12 );
Table created.
SQL> insert into appointment (appt_id, counselor, client_id, room, start_time, duration)
2 values(1,'Quinn Fowler',100,1,to_date('2016-2-15 1:00 pm','yyyy-mm-dd hh:mi am'),55);
1 row created.
SQL>
SQL>
SQL> select APPT_ID, COUNSELOR, CLIENT_ID, ROOM, START_TIME, DURATION from appointment;
APPT_ID COUNSELOR CLIENT_ID ROOM START_TIME DURATION
---------- -------------------- ---------- ---------- -------------------- ----------
1 Quinn Fowler 100 1 15-FEB-2016 01:00 PM 55
SQL> select APPT_ID, CREATE_DATE, CREATED_BY, CHANGE_DATE, CHANGED_BY from appointment;
APPT_ID CREATE_DATE CREATED_BY CHANGE_DATE CHANGED_BY
---------- -------------------- ------------------------------ -------------------- ------------------------------
1
SQL> select sysdate from dual;
SYSDATE
--------------------
23-JAN-2016 07:35 PM
SQL> select user from dual;
USER
------------------------------
GOVE
SQL> CREATE OR REPLACE TRIGGER appointment_creation
2 BEFORE INSERT ON appointment
3 FOR EACH ROW
4 BEGIN
5 :new.create_date := sysdate;
6 :new.created_by := user;
7 END;
8 /
Trigger created.
SQL> insert into appointment (appt_id, counselor, client_id, room, start_time, duration)
2 values(2,'Quinn Fowler',101,1,to_date('2016-2-15 2:00 pm','yyyy-mm-dd hh:mi am'),55);
1 row created.
SQL> insert into appointment (appt_id, counselor, client_id, room, start_time, duration)
2 values(3,'Quinn Fowler',102,1, to_date('2016-2-15 3:00 pm','yyyy-mm-dd hh:mi am'),55);
1 row created.
SQL> select APPT_ID, COUNSELOR, CLIENT_ID, ROOM, START_TIME, DURATION 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 55
3 Quinn Fowler 102 1 15-FEB-2016 03:00 PM 55
SQL> select APPT_ID, CREATE_DATE, CREATED_BY, CHANGE_DATE, CHANGED_BY from appointment;
APPT_ID CREATE_DATE CREATED_BY CHANGE_DATE CHANGED_BY
---------- -------------------- ------------------------------ -------------------- ------------------------------
1
2 23-JAN-2016 07:37 PM GOVE
3 23-JAN-2016 07:37 PM GOVE
SQL> CREATE OR REPLACE TRIGGER appointment_modification
2 BEFORE UPDATE ON appointment
3 FOR EACH ROW
4 BEGIN
5 :new.change_date := sysdate;
6 :new.changed_by := user;
7 END;
8 /
Trigger created.
SQL> update appointment set start_time = to_date('2016-2-16 2:00 pm','yyyy-mm-dd hh:mi am') where appt_id = 2;
1 row updated.
SQL> select APPT_ID, COUNSELOR, CLIENT_ID, ROOM, START_TIME, DURATION 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 16-FEB-2016 02:00 PM 55
3 Quinn Fowler 102 1 15-FEB-2016 03:00 PM 55
SQL> select APPT_ID, CREATE_DATE, CREATED_BY, CHANGE_DATE, CHANGED_BY from appointment;
APPT_ID CREATE_DATE CREATED_BY CHANGE_DATE CHANGED_BY
---------- -------------------- ------------------------------ -------------------- ------------------------------
1
2 23-JAN-2016 07:37 PM GOVE 23-JAN-2016 07:39 PM GOVE
3 23-JAN-2016 07:37 PM GOVE