-- last modified: 12-1-10

----------------------------------------------------------------------------
-- TRIGGER EXAMPLE #3
----------------------------------------------------------------------------
-- before delete a prof, see if he/she has any advisees;
-- if so, make it so they have *no* advisor, so deletion
-- can proceed.
--
-- also available separately as trigger3.sql and clear_advisor.sql
----------------------------------------------------------------------------

create or replace trigger clear_advisor
before delete
on prof
for each row

begin
	-- students with the advisor-to-be-deleted
	-- are now set to have *no* advisor

	update stud
	set    advised_by = null
	where  advised_by = :old.prof_id;

        -- advisee_ct is the new table added to get
        -- the second trigger to work on updates, also;
	-- this professor need no longer be there

	delete from advisee_ct
	where  prof_id = :old.prof_id;
end;
/
show errors