0

I want to have a trigger that no more than 3 employees work on a given project this is the code that i have but doesn't work very well, sometimes work and sometimes doesn't work.

create or replace trigger t_maxim_empleats_projecte
before insert or update of codi_proj
on empleats
for each row
declare
contador number(5);
begin
select count(codi_proj) into contador from empleats where codi_proj= :new.codi_proj;
if contador > 3 then
RAISE_APPLICATION_ERROR(-20000, 'No poden haver-hi més de 3 empleats en un
mateix projecte.');
end if;
end;

This is all i have in my SQL tables:

create table Projectes(
    codi_proj   number(5), 
    nom_proj    varchar2(25),
    pressupost  number(10,2),
    primary key (codi_proj)
);

create table Empleats(
    codi_emp    number(5),
    nom_emp varchar2(15),
    sou     number(10,2),
    codi_dept   number(5),
    codi_proj   number(5),
    data_alta   date,
    primary key (codi_emp),
        foreign key (codi_dept)   references Departaments(codi_dept) on delete set null,
        foreign key (codi_proj)   references Projectes(codi_proj) on delete set null
);

insert into projectes(codi_proj, nom_proj, pressupost)
values (1, 'Daisy', 240000);

insert into projectes(codi_proj, nom_proj, pressupost)
values (2, 'CLAM', 63000);

insert into projectes(codi_proj, nom_proj, pressupost)
values (3, 'Vocal Processor', 600000);

insert into empleats(codi_emp, nom_emp, sou, codi_dept, codi_proj,data_alta)
values (1, 'Maria', 21000, 1, 1,TO_DATE('10/10/1980','dd/mm/yyyy'));

insert into empleats(codi_emp, nom_emp, sou, codi_dept, codi_proj,data_alta)
values (2, 'Josep', 18000, 1, 1,TO_DATE('01/08/1982','dd/mm/yyyy'));

insert into empleats(codi_emp, nom_emp, sou, codi_dept, codi_proj,data_alta)
values (3, 'Ramon', 48000, 4, 2,TO_DATE('05/04/2005','dd/mm/yyyy'));

APC
  • 144,005
  • 19
  • 170
  • 281
  • *"sometimes work and sometimes doesn't work"*. You need to run some tests using **controlled sets of data**. Once you understand the starting state of the tables you can investigate which inputs work and which don't, and then you should understand how to fix your problem. Besides which it's not very helpful to us if you just say *"sometimes work and sometimes doesn't work"*. You need to describe how it doesn't work: what happens? do you get an error you're not expecting? or not getting an error you were hoping for? – APC May 05 '20 at 11:58
  • You may find [this answer to a previous question](https://stackoverflow.com/a/47280427/146325) offers some insights. – APC May 05 '20 at 12:01
  • Please check the answer and feedback. – VN'sCorner May 05 '20 at 13:40

3 Answers3

0

There are two issues.

Issue 1 : I guess you are expecting the trigger to fire when count(codi_proj) is greater than 3.When you are inserting the 4th record the trigger will not fire as the trigger fires at "before insert" which means it gets count as 3 and will not consider the current record being inserted.So to fix it you can modify the trigger to do a greater than or equal to contador >= 3 the code will work perfectly.

Issue 2 : There is another issue with the approach of testing.If you are testing the trigger by inserting the records to projectes as described in the post you must have a commit after each insert(or DML)operation.Trigger like any other database object reads from the persisted state of data.

VN'sCorner
  • 1,532
  • 1
  • 9
  • 13
  • Your Issue 2 is not quite correct; each DML does not require a commit. While it is true that no other transaction can see uncommitted changes the issuing transaction can see the result of DMLs it has issued, including any action taken in triggers (except for the row currently being processed). See [Fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=8e1faa5cb146600d29f262eef97dbe0f). There are no commits, but selects do show table changes. Actually committing after each DML is poor practice. – Belayer May 05 '20 at 22:03
  • @Belayer - "each DML does not require a commit." - The reason why mentioned to commit is in real world(or Prod System) OLTP application GUI each session created by user will be performing the insertion followed by commit for a positive flow. Inserting from same session will not mimic real world scenario. I agree that the persisted state objects for any session means all committed session that performs DML on object in question + uncommitted DML on current session. – VN'sCorner May 06 '20 at 12:14
  • I guess that is a matter of tools and design. In the real world I always choose tools and design so that transaction may contain many DML statements to complete. My objection is the statement "you must have a commit after each insert" That is not true within a single transaction. – Belayer May 07 '20 at 02:00
0

Thanks to your help I've resolved partially the issue. However, when I insert it, it works but when I update it, it doesn't works when I have more than 3 workers in the same project. I think it doesn't work because the uptade needs a procedure that run another procedure

  • No other procedure is needed. Did you change the trigger events I sent as version only fired on insert "after insert on employees". To include updates change to "after insert or update on employees". Unfortunately, statement level cannot be set to fire on only update of a certain column. – Belayer May 06 '20 at 00:59
0

You are not going to do what you want at least with the current row level trigger approach. The reason being that a row level trigger can not access the table that caused it to fire. As a result the statement "select ... from empleats ... will throw the exception "ORA-04091: table is mutating, trigger/function may not see it" because the trigger fires in response to DML against table empleats. A better process is to make this check in a Business Rules layer, or even the application level. However, if you insist on a trigger, it can be accomplished via an after statement trigger. That trigger does have to (or at least should) deal with possibility multiple projects exceeding the max employee limit. So (See fiddle):

create or replace trigger limit_3_emp_per_proj_ais
           after insert on employees 
declare
   k_new_msg_line    constant            varchar2(3)  := chr(10) || '  ';
   k_max_emp_message constant            varchar2(80) := 
       'No rows inserted!' || k_new_msg_line || 'Following Projects have exceed max of 3 employees:'; 
   cursor proj_over_3_emp is
         select proj.name, proj.proj_id, count(*)
           from projects  proj 
           join employees emp
             on (emp.proj_id = proj.proj_id)
          group by proj.proj_id, proj.name    
         having count(*) > 3 
          order by proj.proj_id;

   l_proj_exceeds_3_emp                varchar2(3500) := null;           
begin 
   for proj_emp in proj_over_3_emp  
   loop 
       l_proj_exceeds_3_emp := l_proj_exceeds_3_emp || k_new_msg_line ||
                               proj_emp.name || '(' || proj_emp.proj_id || ')';
   end loop; 

   if l_proj_exceeds_3_emp is not null
   then 
      raise_application_error( -20199,k_max_emp_message || l_proj_exceeds_3_emp);
   end if;

end limit_3_emp_per_proj_ais;
Belayer
  • 13,578
  • 2
  • 11
  • 22