set echo off

prompt "Example trigger trig1.sql, page 46 Oracle/SQL Tutorial"
prompt
prompt "Creating additional table SALS containing salary ranges..."

set echo on

DROP TABLE SALS;
CREATE TABLE SALS
        (JOB VARCHAR2(9) primary key,
         MINSAL NUMBER(7,2),
         MAXSAL NUMBER(7,2)
        );

INSERT INTO SALS VALUES ('CLERK', 800, 1300);
INSERT INTO SALS VALUES ('ANALYST', 3000, 3500);
INSERT INTO SALS VALUES ('SALESMAN', 1250, 1600);
INSERT INTO SALS VALUES ('MANAGER', 2450, 2975);
INSERT INTO SALS VALUES ('PRESIDENT', 5000, 5500);

create or replace trigger check_salary_EMP
after insert or update of SAL, JOB on EMP
for each row
when (new.JOB != 'PRESIDENT')
declare
	minsal number;
	maxsal number;
begin
	-- retrieve minimum and maximum salary for JOB
	select MINSAL, MAXSAL into minsal, maxsal from SALS
	where JOB = :new.JOB;
	-- If the new salary has been decreased or does not lie
	-- within the salary range raise an exception
	if :new.SAL < minsal or :new.SAL > maxsal then
		raise_application_error(-20225, 'Salary range exceeded');
	elsif :new.SAL < :old.SAL then
		raise_application_error(-20230, 'Salary has been decreased');
	elsif :new.SAL > 1.1*:old.SAL then
		raise_application_error(-20235, 'More than 10% salary increase');
	end if;
end;
/