/*===
    PROOF of CONCEPT: a fourth version of our first trigger!

    trigger: empl_trig: before update on empl

    purpose: if serveroutput is set to on,
        before each update ***of a row*** in empl:

        if the update is trying to set the hiredate
        to sysdate -- to the date the update is being done --
        then it raises and error, complains, and PREVENTS that
        update;

        otherwise, it prints a message to the screen
        that says an update is happening, and printing
        the hiredate in that row BEFORE and AFTER that
        update, and giving the number of current
        departments (to demo you can query a DIFFERENT table
        than the one the trigger is fired on)

===*/

create or replace trigger empl_trig
    before update
    on empl
    for each row
declare
    latest_hiredate date;
    num_depts       integer;
begin
    dbms_output.put_line('=======================');
    dbms_output.put_line('ABOUT to update an empl row!');

    /*===
        if update includes trying to update the hiredate
        to the sysdate -- to the date this update is being
        done -- then prevent that update from happening
    ===*/

    if (:new.hiredate = sysdate) then

         raise_application_error( -20000,
	       'you cannot change a hiredate to today''s date');
    end if;

    /*=== otherwise, OK to proceed with update ===*/

    dbms_output.put_line('hiredate BEFORE: ' || :old.hiredate);
    dbms_output.put_line('hiredate AFTER:  ' || :new.hiredate);

    /* UNCOMMENT to see the error if a trigger tries to
       query the table it is triggered upon

    select max(hiredate)
    into latest_hiredate
    from empl;
    */

    /*===
        can select from a table OTHER than the one the trigger
        is fired on, however
    ===*/

    select count(*)
    into num_depts
    from dept;

    dbms_output.put_line('num_depts is: ' || num_depts);
end;
/
show errors

set serveroutput on

prompt
prompt ========
prompt exercising trigger4.sql's FOURTH version of trigger empl_trig

commit;

prompt ========
prompt try to update all rows to have a hiredate of today's date;
prompt SHOULD see the error raised complaining and preventing this
prompt update:

update empl
set hiredate = sysdate;

prompt contents of empl after attempted update
prompt (hiredates should NOT be today's date)
prompt ========

select *
from   empl;

prompt ========
prompt this time, trying to update all hiredates to one week ago,
prompt SHOULD see messages about updating empl rows,
prompt one message per row updated,
prompt including the hiredate for that row before and after being updated,
prompt and the number of departments:
prompt

update empl
set hiredate = sysdate - 7;

prompt contents of empl after successful update (and before rollback!):
prompt (hiredates now should all be one week ago)
prompt ========

select *
from   empl;

rollback;