Home » SQL & PL/SQL » SQL & PL/SQL » Controlling number of employees in specific department
Controlling number of employees in specific department [message #39204] Tue, 25 June 2002 23:49 Go to next message
menny klimian
Messages: 4
Registered: May 2002
Junior Member
Hi everyone
I'm trying to create a trigger that would prevent
to put more then 5 employees in the same department.
Because of a "Mutating" problem I cannot think of a way.
If it's a row trigger I cannot use an sql stetament on the emp table,and if it's a stetament trigger I cannot use the
:New.deptno...
Can someone help ??
Re: Controlling number of employees in specific department [message #39207 is a reply to message #39204] Wed, 26 June 2002 00:10 Go to previous messageGo to next message
Vikas Gupta
Messages: 115
Registered: February 2002
Senior Member
Hello Menny,

Check this site for the Mutuating table problem:

http://www.fortunecity.com/skyscraper/oracle/699/orahtml/oracle/mutate.html

This should help you.

Regards,

Vikas.
Re: Controlling number of employees in specific department [message #39211 is a reply to message #39204] Wed, 26 June 2002 09:19 Go to previous message
Mahesh Rajendran
Messages: 10690
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
this may help you.

SQL> get tr1
line 6 truncated.
  1  create or replace package projPak
  2      as
  3        dno number;
  4*     end;
  5  /

Package created.

SQL> get tr2
line 8 truncated.
  1  create or replace trigger RcheckPCount
  2      before insert or update on emp
  3      for each row
  4      begin
  5        projPak.dno := :new.deptno;
  6* end;
  7  /

Trigger created.

SQL> get trig_mgr
  1  create or replace trigger ScheckPCount
  2  after insert or update on emp
  3  declare
  4  v_cnt number;
  5  begin
  6  select count(*) into v_cnt from emp
  7     where deptno = projPak.dno;
  8  if v_cnt >5 then
  9     raise_application_error
 10     (-20100,'This department cannot have more than five employees');
 11     dbms_output.put_line(v_cnt);
 12     end if;
 13* end;
SQL> /
Trigger created.


first welll check the present count of employees(departmentwise).
SQL> ed
Wrote file afiedt.buf
  1* select deptno,count(empno) from emp group by deptno
SQL> /

    DEPTNO COUNT(EMPNO)
---------- ------------
        10            3
        20            5
        30            6

SQL> ed
Wrote file afiedt.buf

  1  INSERT INTO EMP VALUES
  2* (1111,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,10)
SQL> /

1 row created.

SQL> ed
Wrote file afiedt.buf

  1  INSERT INTO EMP VALUES
  2* (1112,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,10)
SQL> /

1 row created.

SQL> ed
Wrote file afiedt.buf

  1  INSERT INTO EMP VALUES
  2* (1113,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,10)
SQL> /
INSERT INTO EMP VALUES
            *
ERROR at line 1:
ORA-20100: This department cannot have more than five employees
ORA-06512: at "MAG.SCHECKPCOUNT", line 7
ORA-04088: error during execution of trigger 'MAG.SCHECKPCOUNT'

And this goes good for updates also
SQL> update emp set deptno=10;
update emp set deptno=10
       *
ERROR at line 1:
ORA-20100: This department cannot have more than five employees
ORA-06512: at "MAG.SCHECKPCOUNT", line 7
ORA-04088: error during execution of trigger 'MAG.SCHECKPCOUNT'
Previous Topic: sql
Next Topic: DB Links
Goto Forum:
  


Current Time: Tue Oct 22 03:29:02 CDT 2019