-- last modified: 12-1-10

----------------------------------------------------------------------------
-- TRIGGER EXAMPLE #4
----------------------------------------------------------------------------
-- prevent insertions or updates into stud if they
-- will result in some prof having more than 3 advisees
--
-- also available separately as trigger4.sql and limit_advisees.sql
----------------------------------------------------------------------------

create or replace trigger limit_advisees
before insert or update
on stud
for each row

declare
	curr_num_advisees	integer;
	advisor			varchar2(50);
begin
	-- new would-be advisor is null? just update advisee_ct,
	-- IF necessary

	if :new.advised_by is null then
		-- if this is an update, and there WAS an advisor
		-- before, then their advisee_ct must be updated

		if updating and :old.advised_by is not null then
			update advisee_ct
			set    num_advisees = num_advisees-1
			where  prof_id = :old.advised_by;
		end if;

	-- but, if new would-be advisor is NOT null, see
	-- if action can be permitted --- if this is a
	-- 4th advisee; 

	else 
		-- how many advisees DOES the would-be new advisor have?

		select	num_advisees
		into    curr_num_advisees
		from	advisee_ct
		where	prof_id = :new.advised_by;		
		
		-- if this new advisee would be 4th or more, forbid it

		if curr_num_advisees >= 3 then

			select  prof_fname || ' ' || prof_lname
			into	advisor
			from	prof
			where prof_id = :new.advised_by;

			raise_application_error(-20600, 'Student ' ||
				:new.stud_id || ' cannot be inserted with ' ||
				' advisor of ' || advisor || ', because ' ||
				'he/she already has ' || curr_num_advisees ||
				' advisees');

		else
			-- okay to allow this change with this advisor;

			update advisee_ct
			set    num_advisees = num_advisees + 1
			where  prof_id = :new.advised_by;

			-- whoops! was there a previous advisor losing a stud?
			if updating then
				if :old.advised_by is not null then
					update advisee_ct
					set num_advisees = num_advisees - 1
					where prof_id = :old.advised_by;
				end if;
			end if;

		end if;
	end if;
end;
/
show errors