set echo off

prompt "Example trigger trig3.sql, page 47 Oracle/SQL Tutorial"
prompt
prompt "Adding attribute BUDGET to the table DEPT ..."

set echo on

ALTER TABLE DEPT
       ADD BUDGET NUMBER(8,2);

UPDATE DEPT set BUDGET = 10000 where DEPTNO = 10;
UPDATE DEPT set BUDGET = 15000 where DEPTNO = 20;
UPDATE DEPT set BUDGET = 10000 where DEPTNO = 30;
UPDATE DEPT set BUDGET = 5000 where DEPTNO = 40;

create or replace trigger check_budget_EMP
after insert or update of SAL, DEPTNO  on EMP
declare
	cursor DEPT_CUR is
	    select DEPTNO, BUDGET from DEPT;
	DNO 	 DEPT.DEPTNO%TYPE;
	ALLSAL 	 DEPT.BUDGET%TYPE; 
	DEPT_SAL  number;
begin
  open DEPT_CUR;
  loop
  	fetch DEPT_CUR  into DNO, ALLSAL;
  	exit when DEPT_CUR%NOTFOUND;
	select sum(SAL)  into DEPT_SAL  from EMP
	where DEPTNO = DNO;
	if DEPT_SAL > ALLSAL then
		raise_application_error(-20325, 'Total of salaries in the department '|| 						to_char(DNO) || ' exceeds budget'); 
  	end if;
  end loop;
  close DEPT_CUR;
end;
/