/*===
    328lect06-1.sql 
    *   creates and demonstrates a PL/SQL trigger, empl_trig

    *   after class, added the described extension so that
        it fires before an update OR before a deletion
    
    by: Sharon Tuttle
    last modified: 2024-02-20
===*/

/*===
    our first trigger!

    trigger: empl_trig
    purpose: fires BEFORE each UPDATE or DELETE to table EMPL;

             if before an UPDATE:
             *   prints a message to the screen saying
                 about to update an empl row
             
             *   PREVENTS the update (atomic -- so it was
                 completely DONE, or completely NOT done)
                 if will change/update an empl's hiredate to
                 the current day's date, using
                 raise_application_error

             *   prints to the screen their last name and
                 hiredate before and after the update

             if before a DELETE:
             *   prints a message to the screen saying 
                 about to delete an empl row

             *   IF deleted empl is a customer rep,
                 updates affected customer(s) so their 
                 empl_rep column is NULL (so this 
                 deletion WILL be permitted)
===*/

create or replace trigger empl_trig
    before update OR delete
    on empl
    for each row
declare
    latest_hiredate date;
begin
    -- kluge to print a blank line

    dbms_output.put(chr(10));

    dbms_output.put_line('===== trigger empl_trig fired =====');

    if UPDATING then
        dbms_output.put_line('ABOUT to update an empl row!');

        /*===
            we decide an existing employee's hiredate may NOT
            be changed to the date an update is done...
        ===*/

        if (:new.hiredate = sysdate) then
    
            raise_application_error( -20000,
	        'will not allow updating a hiredate to today''s date');
        end if;

        /*=== if reach here, OK to allow update to proceed ===*/

        dbms_output.put_line('empl_last_name and hiredate WERE: '
                             || :old.empl_last_name || ' '
                             || :old.hiredate);

        dbms_output.put_line('empl_last_name and hiredate NOW are: '
                             || :new.empl_last_name || ' '
                             || :new.hiredate);

    /*=== 
        since trigger fires if update OR delete, could JUST use else
        here, but want to *demo* ELSIF, am using it here.

        CHANGE to ELSE IF to see the error you get for that!
    ===*/

    elsif deleting then

        dbms_output.put_line('ABOUT to delete an empl row!');

        /*===
            if any customers have the to-be-deleted empl as their
            empl_rep, change their empl_rep to NULL, so 
            referential integrity constraint won't prevent this
            deletion
        ===*/

        for cust_row in (select cust_id
                         from customer
                         where empl_rep = :old.empl_num)
        loop
            update customer
            set empl_rep = NULL
            where cust_id = cust_row.cust_id;
        end loop;

    end if;
    
    /*== IS it true that you generally cannot query the triggering
         table? 
         UNCOMMENT to see the "is mutating" error message:

    select max(hiredate)
    into latest_hiredate
    from empl;
    ==*/    
end;
/
show errors

/*=== TESTING trigger empl_trig ===*/

set serveroutput on

prompt ==================
prompt TESTING empl_trig

/*=== 
    commit current state of db, to roll back to
    after our tests/experiments!
===*/

commit;

prompt ========
prompt SHOULD see 14 messages saying about to update
prompt all 14 rows in the empl table,
prompt changing their hiredates to one week ago
prompt

update empl
set hiredate = sysdate - 7;

prompt ========
prompt SHOULD see their hiredates ARE indeed changed
prompt after the above update, to one week ago

select empl_last_name, hiredate
from   empl;

prompt =======
prompt BUT, this update will fail, because trigger
prompt PREVENTS the update if updating hiredate to the
prompt current date:
prompt

update empl
set hiredate = sysdate;

prompt ======
prompt SHOULD still see hiredates of ONE WEEK ago,
prompt NOT today:

select empl_last_name, hiredate
from   empl;

prompt ============
prompt customers and their empl reps
prompt BEFORE try to delete one empl who is a customer rep and one who is not:

select  cust_lname, empl_rep
from customer; 

delete empl
where empl_last_name = 'Miller';

delete empl
where empl_num = '7499';

prompt ============
prompt customers and their empl reps
prompt AFTER try to delete an empl who is a customer rep and another who is not
prompt (Firstly and Thirdly now have no empl rep):

select  cust_lname, empl_rep
from customer;   

prompt ==========
prompt and should see only 12 employees, with NO Miller and NO Michaels:

select empl_num, empl_last_name
from empl;

/*===
    rolling back db state to before these tests/experiments
===*/

rollback;