| An employee should never manage more than 5 employees [message #38821] |
Thu, 16 May 2002 18:10  |
Sid
Messages: 38
Registered: May 1999
|
Member |
|
|
I need to apply this business rule using a trigger:
"An employee should never be manager of more than five employees."
I have written code and put this a package. I have put the code in a package because there is cursor that calculates the number of managers.
I have tested this on the employer emp class but it does not seem to work.
Here is the code for the package:
CREATE OR REPLACE PACKAGE mgr_constraints_pkg
IS
PROCEDURE add_mgrlist(v_mgr IN emp.mgr%TYPE);
PROCEDURE clean_mgrlist;
PROCEDURE check_mgr;
END mgr_constraints_pkg;
/
CREATE OR REPLACE PACKAGE body mgr_constraints_pkg
IS
v_list_length NUMBER := 0; --list of number of managers
TYPE mgr_table_type IS TABLE OF
emp.mgr%TYPE
INDEX BY BINARY_INTEGER;
CURSOR mgr_cursor IS
SELECT distinct mgr
FROM emp
ORDER BY mgr;
mgrlist_table mgr_table_type;
PROCEDURE clean_mgrlist
IS
BEGIN
mgrlist_table.delete;
v_list_length := 0;
END clean_mgrlist;
PROCEDURE add_mgrlist(v_mgr IN emp.mgr%TYPE)
IS
v_new_mgr BOOLEAN := TRUE;
BEGIN
FOR i IN 1..v_list_length LOOP
if mgrlist_table(i) = v_mgr THEN
V_NEW_MGR := FALSE;
END IF;
END LOOP;
IF v_new_mgr THEN
v_list_length := v_list_length + 1;
mgrlist_table(v_list_length) := v_mgr;
END IF;
END add_mgrlist;
-- ***** An employer should never manage more then five employers *****
PROCEDURE check_mgr
IS
v_total_emps NUMBER;
v_max_number_emps CONSTANT NUMBER := 5;
BEGIN
FOR i IN 1..v_list_length LOOP
SELECT COUNT(*)
INTO v_total_emps
FROM emp
WHERE mgr = mgrlist_table(i);
IF v_total_emps > v_max_number_emps THEN
RAISE_APPLICATION_ERROR(-20000, 'Max number of emps exceeded for ' || TO_CHAR(mgrlist_table(i)));
END IF;
END LOOP;
END check_mgr;
BEGIN
FOR mgr_rec IN mgr_cursor LOOP
add_mgrlist(mgr_rec.mgr);
END LOOP;
END mgr_constraints_pkg;
/
Here is my trigger:
create or replace trigger max_supervise5
before insert or update or delete on emp
for each row
begin
mgr_constraints_pkg.check_mgr;
end;
/
Why don't I get an error,
for example employee Blake currently manages 5 employees:
SQL> select count(*) from emp where mgr = 7698;
COUNT(*)
----------
5
So under business rule he should be prevented from supervising the 6th employee.
SQL> insert into emp
2 VALUES('8107','DAVID', 'ASSISTANT', '7698', '15-MAY-02', '5000', NULL, '30');
1 row created. --this should return with the raise_application_error but it doesn't. WHY????
|
|
|
|
|
|
| Re: An employee should never manage more than 5 employees [message #38876 is a reply to message #38821] |
Thu, 23 May 2002 08:11  |
menny klimian
Messages: 4
Registered: May 2002
|
Junior Member |
|
|
Hi Sid
I think I found a better easy way to apply your rule
(prevent more than 5 ... )
It includes one procedure and one trigger - check this out.
create or replace trigger check_trig
before insert or update or delete on emp
for each row
declare
v_mgr number;
begin
v_mgr:=:new.mgr;
check_mgr(v_mgr);
end;
/
set serveroutput on
create or replace procedure check_mgr
(v_mgr number)
IS
v_count number;
BEGIN
select count(*) into v_count
from emp
where mgr=v_mgr;
if v_count=5 then
raise_application_error(-20100,'An employee cannot be manager of more than five
employees');
end if;
END;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
