/*===
    PROOF of CONCEPT: a third 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,
        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!');

    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 trigger3.sql's THIRD version of trigger empl_trig

commit;

prompt =====
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;

prompt contents of empl after updates (and before rollback!):
prompt ========

select *
from   empl;

rollback;