Hi there,
I have written a trigger that does not allow more than two 'Full' ranked professors as part of the faculty (For example, trigger should fire if a new (third) Full professor is added or rank is updated to Full for one of the existing Associate professors.)
It doesn't compile and does not let me know add any data to my table at all. It only needs to be used once. Do I use the statement level trigger for this?
Also, at the moment it does not let me update or insert Professor Rank at all, whether its Full or Associate. How would I fix that?
I have also been told that before/after and that my logic of comparison is wrong. Please help!
Here is the trigger:
CREATE OR REPLACE TRIGGER TRG_PROF_RANK
before insert or update of F_RANK
on FACULTY
--FOR EACH ROW
DECLARE
FULL_RANK_COUNT integer;
MAX_FULL_RANK_COUNT number :=2;
begin
select count(*) into FULL_RANK_COUNT
from FACULTY
where F_RANK ='Full';
if FULL_RANK_COUNT < MAX_FULL_RANK_COUNT
or then
return;
else
if (FULL_RANK_COUNT >= MAX_FULL_RANK_COUNT) then
RAISE_APPLICATION_ERROR (-20000, 'Can only have 2 professors with ranking "Full".');
end if;
end if;
end;
/
insert into FACULTY values(6, 'John', 'Bonny', 'M', 13, '4079347153', 'Associate', 80000, 2, 6034, Null);
update FACULTY set F_RANK = 'Full' where F_ID = 1;
These statements produce the following error: