set echo off

prompt "Example trigger trig2.sql, page 47 Oracle/SQL Tutorial"
prompt
set echo on

create or replace trigger check_salary_SALS
before update or delete on SALS
for each row
when ( new.MINSAL > old.MINSAL  or
       new.MAXSAL <  old.MAXSAL or
       new.MAXSAL is null) 
-- only restricting a salary range can cause a constraint violation 
declare
	job_emps  number;
begin
	if deleting then -- Does there still exist an employee having the deleted job
		select count(*)  into job_emps  from EMP
		where JOB =  :old.JOB;
		if job_emps != 0  then
		raise_application_error(-20240,' There still exist employees with the job ' ||  :old.JOB); 
  		end if ;
	end if ;
	if updating then
	-- Are there employees whose salary does not lie within the
        --  modified salary range ?
		select count(*)  into job_emps  from EMP
		where JOB =  :new.JOB  and SAL not between :new.MINSAL and :new.MAXSAL;
		if job_emps != 0  then -- restore old salary ranges
			:new.MINSAL :=  :old.MINSAL;
			:new.MAXSAL :=  :old.MAXSAL;
		end if ;
	end if ;
end;
/