CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsqltriggers
Python241820
Python241820

Reputation: 1537

Compound trigger invalid identifier

I have the following table:

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

I have added these data:

insert into lessons values (001,'Peter','Thomas',to_date('2015-12-15','YYYY-MMDD'),to_date('2015-12-22','YYYY-MM-DD'));
insert into lessons values (002,'Eli','Alice',to_date('2015-06-16','YYYY-MMDD'),to_date('2015-06-23','YYYY-MM-DD'));
insert into lessons values (003,'Daniel','Thomas',to_date('2015-08-15','YYYY-MMDD'),to_date('2015-08-20','YYYY-MM-DD'));
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'));

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'));

I have this code in Oracle, with which I try to get to make a trigger that does not allow me to add students who have teachers who overlap in time, like "Peter" or "Eli".

CREATE OR REPLACE TRIGGER lessons_trg
FOR INSERT OR UPDATE ON lessons
REFERENCING NEW AS New OLD AS Old
COMPOUND TRIGGER

 TYPE tbl_t IS RECORD(
 name_teacher VARCHAR2(9),
 start_lesson date,
 end_lesson date
 );
 TYPE tbl_typ IS  TABLE OF tbl_t;
 tbl tbl_typ;

  BEFORE STATEMENT IS
  BEGIN
   SELECT tbl_t(name_teacher, start_lesson, end_lesson)
   BULK COLLECT INTO tbl
   FROM lessons;
  END BEFORE STATEMENT;

  BEFORE EACH ROW IS
  v_count number;
  BEGIN
    SELECT COUNT(1)
    INTO v_count
    FROM TABLE(tbl) t
    WHERE t.name_teacher = :NEW.name_teacher
    AND :NEW.start_lesson BETWEEN t.start_lesson AND t.end_lesson
    AND :NEW.end_lesson BETWEEN t.start_lesson AND t.end_lesson;

    IF v_count > 0 THEN
      RAISE_APPLICATION_ERROR(-20000, 'Error');
    END IF;
  END BEFORE EACH ROW;
END;
/

The problem is that oracle give me the followings errors:

Error(13,11): PL/SQL: ORA-00904: "TBL_T": invalid identifier
Error(23,10): PL/SQL: ORA-22905: cannot access rows from a non-nested table item
Error(23,16): PLS-00642: local collection types not allowed in SQL statements

Thanks for the help and good day :)

Upvotes: 0

Views: 119

Answers (2)

Kacper
Kacper

Reputation: 4818

create or replace 
TYPE tbl_t as object(
 name_teacher VARCHAR2(9),
 start_lesson date,
 end_lesson date
 );
 /
 create TYPE tbl_typ IS  TABLE OF tbl_t;
 /
create or replace 
trigger lessons_trg
FOR INSERT OR UPDATE ON lessons
REFERENCING NEW AS New OLD AS Old
COMPOUND TRIGGER


 tbl tbl_typ;

  BEFORE STATEMENT IS
  BEGIN
   SELECT tbl_t(name_teacher, start_lesson, end_lesson)
   BULK COLLECT INTO tbl
   FROM lessons;
  END BEFORE STATEMENT;

  BEFORE EACH ROW IS
  v_count number;
  BEGIN
    SELECT COUNT(1)
    INTO v_count
    FROM TABLE(tbl) t
    WHERE t.name_teacher = :NEW.name_teacher
    AND :NEW.start_lesson BETWEEN t.start_lesson AND t.end_lesson
    AND :NEW.end_lesson BETWEEN t.start_lesson AND t.end_lesson;

    IF v_count > 0 THEN
      RAISE_APPLICATION_ERROR(-20000, 'Error');
    END IF;
  END BEFORE EACH ROW;
END;
/
 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'));
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'));

This should work

Upvotes: 1

0xdb
0xdb

Reputation: 3697

You need to declare your types explicitly as database objects, not as components in a trigger.
Try something like this:

create or replace TYPE tbl_t IS object(
    name_teacher VARCHAR2(9),
    start_lesson date,
    end_lesson date
    );
 /
 create or replace TYPE tbl_typ IS TABLE OF tbl_t;
 /

Don't forget comment the type declarations in the trigger body.

Upvotes: 2

Related Questions