SQL> @ trigger4
Trigger created.
No errors.
========
exercising trigger4.sql's FOURTH version of trigger empl_trig
Commit complete.
========
try to update all rows to have a hiredate of today's date
SHOULD see the error raised complaining and preventing this
update:
=======================
ABOUT to update an empl row!
update empl
*
ERROR at line 1:
ORA-20000: you cannot change a hiredate to today's date
ORA-06512: at "ST10.EMPL_TRIG", line 16
ORA-04088: error during execution of trigger 'ST10.EMPL_TRIG'
contents of empl after attempted update
(hiredates should NOT be today's date)
========
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7839 King President 17-NOV-11 5000 500
7566 Jones Manager 7839 02-APR-12 2975 200
7698 Blake Manager 7839 01-MAY-13 2850 300
7782 Raimi Manager 7839 09-JUN-12 2450 100
7902 Ford Analyst 7566 03-DEC-12 3000 200
7369 Smith Clerk 7902 17-DEC-12 800 200
7499 Michaels Sales 7698 20-FEB-18 1600 300 300
7521 Ward Sales 7698 22-FEB-19 1250 500 300
7654 Martin Sales 7698 28-SEP-18 1250 1400 300
7788 Scott Analyst 7566 09-NOV-18 3000 200
7844 Turner Sales 7698 08-SEP-19 1500 0 300
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7876 Adams Clerk 7788 23-SEP-18 1100 400
7900 James Clerk 7698 03-DEC-17 950 300
7934 Miller Clerk 7782 23-JAN-16 1300 100
14 rows selected.
========
this time, trying to update all hiredates to one week ago,
SHOULD see messages about updating empl rows,
one message per row updated,
including the hiredate for that row before and after being updated,
and the number of departments:
=======================
ABOUT to update an empl row!
hiredate BEFORE: 17-NOV-11
hiredate AFTER: 18-FEB-25
num_depts is: 5
=======================
ABOUT to update an empl row!
hiredate BEFORE: 02-APR-12
hiredate AFTER: 18-FEB-25
num_depts is: 5
=======================
ABOUT to update an empl row!
hiredate BEFORE: 01-MAY-13
hiredate AFTER: 18-FEB-25
num_depts is: 5
=======================
ABOUT to update an empl row!
hiredate BEFORE: 09-JUN-12
hiredate AFTER: 18-FEB-25
num_depts is: 5
=======================
ABOUT to update an empl row!
hiredate BEFORE: 03-DEC-12
hiredate AFTER: 18-FEB-25
num_depts is: 5
=======================
ABOUT to update an empl row!
hiredate BEFORE: 17-DEC-12
hiredate AFTER: 18-FEB-25
num_depts is: 5
=======================
ABOUT to update an empl row!
hiredate BEFORE: 20-FEB-18
hiredate AFTER: 18-FEB-25
num_depts is: 5
=======================
ABOUT to update an empl row!
hiredate BEFORE: 22-FEB-19
hiredate AFTER: 18-FEB-25
num_depts is: 5
=======================
ABOUT to update an empl row!
hiredate BEFORE: 28-SEP-18
hiredate AFTER: 18-FEB-25
num_depts is: 5
=======================
ABOUT to update an empl row!
hiredate BEFORE: 09-NOV-18
hiredate AFTER: 18-FEB-25
num_depts is: 5
=======================
ABOUT to update an empl row!
hiredate BEFORE: 08-SEP-19
hiredate AFTER: 18-FEB-25
num_depts is: 5
=======================
ABOUT to update an empl row!
hiredate BEFORE: 23-SEP-18
hiredate AFTER: 18-FEB-25
num_depts is: 5
=======================
ABOUT to update an empl row!
hiredate BEFORE: 03-DEC-17
hiredate AFTER: 18-FEB-25
num_depts is: 5
=======================
ABOUT to update an empl row!
hiredate BEFORE: 23-JAN-16
hiredate AFTER: 18-FEB-25
num_depts is: 5
14 rows updated.
contents of empl after successful update (and before rollback!):
(hiredates now should all be one week ago)
========
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7839 King President 18-FEB-25 5000 500
7566 Jones Manager 7839 18-FEB-25 2975 200
7698 Blake Manager 7839 18-FEB-25 2850 300
7782 Raimi Manager 7839 18-FEB-25 2450 100
7902 Ford Analyst 7566 18-FEB-25 3000 200
7369 Smith Clerk 7902 18-FEB-25 800 200
7499 Michaels Sales 7698 18-FEB-25 1600 300 300
7521 Ward Sales 7698 18-FEB-25 1250 500 300
7654 Martin Sales 7698 18-FEB-25 1250 1400 300
7788 Scott Analyst 7566 18-FEB-25 3000 200
7844 Turner Sales 7698 18-FEB-25 1500 0 300
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7876 Adams Clerk 7788 18-FEB-25 1100 400
7900 James Clerk 7698 18-FEB-25 950 300
7934 Miller Clerk 7782 18-FEB-25 1300 100
14 rows selected.
Rollback complete.
SQL> spool off