-- By: Drew Hengehold
-- last modified: 10 March 2025
-- CS 328 - Homework 6

set serveroutput on

spool 328hw6-out.txt

prompt +=+=+=+=+=+=+=+=+=;
prompt Drew Hengehold;
prompt +=+=+=+=+=+=+=+=+=;

/*
trigger: log_salary: before Insert or Update on empl

purpose:updates salary_history with new values to empl
    to record changes in salary
*/

create or replace trigger log_salary 
    after insert OR update 
    on empl 
    for each row

begin
    if inserting then
        insert into salary_history
        values(:new.empl_num, null, :new.salary, :new.hiredate);
    else
        if :new.salary != :old.salary then
            insert into salary_history
            values(:new.empl_num, :old.salary, :new.salary, sysdate);
        end if;
    end if; 
end;
/ 
show errors


/*
    ------ Testing log_salary ------
*/

prompt +=+=+= Testing log_salary +=+=+=
prompt

commit;

prompt +=+=+= Pretest Version of empl and salary_history +=+=+=

select *
from empl;

select *
from salary_history;



prompt +=+=+= Test 1, testing insertion +=+=+=
prompt (inserting Abrams into empl)

insert into empl(empl_num, empl_last_name, job_title, mgr, 
                hiredate, salary, dept_num)
values('9329', 'Abrams', 'Manager', '7839', '17-Nov-2021', 1000.00,  '100');

prompt -+-+-+-+-+-+-+-+-;
prompt You should now see 15 rows in salary_history
prompt -+-+-+-+-+-+-+-+-;

select *
from salary_history;



prompt +=+=+= Test 2, testing updating +=+=+=
prompt (updating Blake salary from 2850 to 3000)

update empl
set salary = 3000
where empl_last_name = 'Blake';

prompt +=+=+= Test 3, testing updating +=+=+=
prompt (updating Ford job_title from Analyst to Clerk)

update empl
set job_title = 'Clerk'
where empl_last_name = 'Ford';

prompt -+-+-+-+-+-+-+-+-;
prompt You should now see Ford with job title clerk and 
prompt Blake with salary 3000 in table empl
prompt -+-+-+-+-+-+-+-+-;

select * 
from empl;

prompt -+-+-+-+-+-+-+-+-;
prompt You should now see 16 rows in salary_history with the 
prompt last entry being from Blake whos salary went from 2850 to 3000
prompt -+-+-+-+-+-+-+-+-;

select *
from salary_history;

rollback; 

spool off