SQL> @ 328lect06-1
Trigger created.
No errors.
==================
TESTING empl_trig
Commit complete.
========
SHOULD see 14 messages saying about to update
all 14 rows in the empl table,
changing their hiredates to one week ago
===== trigger empl_trig fired =====
ABOUT to update an empl row!
empl_last_name and hiredate WERE: King 17-NOV-11
empl_last_name and hiredate NOW are: King 13-FEB-24
===== trigger empl_trig fired =====
ABOUT to update an empl row!
empl_last_name and hiredate WERE: Jones 02-APR-12
empl_last_name and hiredate NOW are: Jones 13-FEB-24
===== trigger empl_trig fired =====
ABOUT to update an empl row!
empl_last_name and hiredate WERE: Blake 01-MAY-13
empl_last_name and hiredate NOW are: Blake 13-FEB-24
===== trigger empl_trig fired =====
ABOUT to update an empl row!
empl_last_name and hiredate WERE: Raimi 09-JUN-12
empl_last_name and hiredate NOW are: Raimi 13-FEB-24
===== trigger empl_trig fired =====
ABOUT to update an empl row!
empl_last_name and hiredate WERE: Ford 03-DEC-12
empl_last_name and hiredate NOW are: Ford 13-FEB-24
===== trigger empl_trig fired =====
ABOUT to update an empl row!
empl_last_name and hiredate WERE: Smith 17-DEC-12
empl_last_name and hiredate NOW are: Smith 13-FEB-24
===== trigger empl_trig fired =====
ABOUT to update an empl row!
empl_last_name and hiredate WERE: Michaels 20-FEB-18
empl_last_name and hiredate NOW are: Michaels 13-FEB-24
===== trigger empl_trig fired =====
ABOUT to update an empl row!
empl_last_name and hiredate WERE: Ward 22-FEB-19
empl_last_name and hiredate NOW are: Ward 13-FEB-24
===== trigger empl_trig fired =====
ABOUT to update an empl row!
empl_last_name and hiredate WERE: Martin 28-SEP-18
empl_last_name and hiredate NOW are: Martin 13-FEB-24
===== trigger empl_trig fired =====
ABOUT to update an empl row!
empl_last_name and hiredate WERE: Scott 09-NOV-18
empl_last_name and hiredate NOW are: Scott 13-FEB-24
===== trigger empl_trig fired =====
ABOUT to update an empl row!
empl_last_name and hiredate WERE: Turner 08-SEP-19
empl_last_name and hiredate NOW are: Turner 13-FEB-24
===== trigger empl_trig fired =====
ABOUT to update an empl row!
empl_last_name and hiredate WERE: Adams 23-SEP-18
empl_last_name and hiredate NOW are: Adams 13-FEB-24
===== trigger empl_trig fired =====
ABOUT to update an empl row!
empl_last_name and hiredate WERE: James 03-DEC-17
empl_last_name and hiredate NOW are: James 13-FEB-24
===== trigger empl_trig fired =====
ABOUT to update an empl row!
empl_last_name and hiredate WERE: Miller 23-JAN-16
empl_last_name and hiredate NOW are: Miller 13-FEB-24
14 rows updated.
========
SHOULD see their hiredates ARE indeed changed
after the above update, to one week ago
EMPL_LAST_NAME HIREDATE
--------------- ---------
King 13-FEB-24
Jones 13-FEB-24
Blake 13-FEB-24
Raimi 13-FEB-24
Ford 13-FEB-24
Smith 13-FEB-24
Michaels 13-FEB-24
Ward 13-FEB-24
Martin 13-FEB-24
Scott 13-FEB-24
Turner 13-FEB-24
EMPL_LAST_NAME HIREDATE
--------------- ---------
Adams 13-FEB-24
James 13-FEB-24
Miller 13-FEB-24
14 rows selected.
=======
BUT, this update will fail, because trigger
PREVENTS the update if updating hiredate to the
current date:
===== trigger empl_trig fired =====
ABOUT to update an empl row!
update empl
*
ERROR at line 1:
ORA-20000: will not allow updating a hiredate to today's date
ORA-06512: at "ST10.EMPL_TRIG", line 20
ORA-04088: error during execution of trigger 'ST10.EMPL_TRIG'
======
SHOULD still see hiredates of ONE WEEK ago,
NOT today:
EMPL_LAST_NAME HIREDATE
--------------- ---------
King 13-FEB-24
Jones 13-FEB-24
Blake 13-FEB-24
Raimi 13-FEB-24
Ford 13-FEB-24
Smith 13-FEB-24
Michaels 13-FEB-24
Ward 13-FEB-24
Martin 13-FEB-24
Scott 13-FEB-24
Turner 13-FEB-24
EMPL_LAST_NAME HIREDATE
--------------- ---------
Adams 13-FEB-24
James 13-FEB-24
Miller 13-FEB-24
14 rows selected.
============
customers and their empl reps
BEFORE try to delete one empl who is a customer rep and one who is not:
CUST_LNAME EMPL
-------------------- ----
Firstly 7499
Secondly 7654
Thirdly 7499
===== trigger empl_trig fired =====
ABOUT to delete an empl row!
1 row deleted.
===== trigger empl_trig fired =====
ABOUT to delete an empl row!
1 row deleted.
============
customers and their empl reps
AFTER try to delete an empl who is a customer rep and another who is not
(Firstly and Thirdly now have no empl rep):
CUST_LNAME EMPL
-------------------- ----
Firstly
Secondly 7654
Thirdly
==========
and should see only 12 employees, with NO Miller and NO Michaels:
EMPL EMPL_LAST_NAME
---- ---------------
7839 King
7566 Jones
7698 Blake
7782 Raimi
7902 Ford
7369 Smith
7521 Ward
7654 Martin
7788 Scott
7844 Turner
7876 Adams
EMPL EMPL_LAST_NAME
---- ---------------
7900 James
12 rows selected.
Rollback complete.
SQL> spool off