CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsqltriggers
Python241820
Python241820

Reputation: 1527

Mutating trigger in Oracle

I create the following tables:

create table lessons(
id number,
name_teacher varchar2(9),
name_student varchar2(40),
start_lesson date, 
end_lesson date
);

I inserted the following datas:

insert into lessons values (001,'Peter','Thomas',to_date('2015-12-15','YYYY-MM-DD'),to_date('2015-12-22','YYYY-MM-DD'));
insert into lessons values (002,'Eli','Alice',to_date('2015-06-16','YYYY-MM-DD'),to_date('2015-06-23','YYYY-MM-DD'));
insert into lessons values (003,'Daniel','Thomas',to_date('2015-08-15','YYYY-MM-DD'),to_date('2015-08-20','YYYY-MM-DD'));

Data that you cant add by the trigger.

insert into lessons values (001,'Peter','Alice',to_date('2015-12-16','YYYY-MM-DD'),to_date('2015-12-25','YYYY-MM-DD'));
insert into lessons values (002,'Eli','Thomas',to_date('2015-06-13','YYYY-MM-DD'),to_date('2015-06-20','YYYY-MM-DD'));

The question is how to make a trigger that does not allow me to add students who have teachers who overlap in time, like "Peter" or "Eli".

--- My problem ---

Oracle returns me an error of mutants tables.

Upvotes: 0

Views: 100

Answers (1)

schurik
schurik

Reputation: 7928

you neen an after insert / update Trigger that fire after the comlete insert or update and not after earch row:

create or replace trigger check_intersections_trg 
on 
 lessons
after insert or update
declare 
  v_res   NUMBER;
begin
  select count(*) 
  into  v_res
  from lessons l1 
  join lessons l2  on l1.name_student = l2.name_student 
                   and l1.start_lesson <= l2.end_lesson
                   and l2.start_lesson <= l1.end_lesson  
  ;
  if v_res > 0 than
   raise_application_error( -20999, 'intersection found');
  end if;
end;
/

Upvotes: 1

Related Questions