How do i create a trigger that needs to check if the new value is on an array in the same table (oracle 11g)

Question

I have a table area and doctor. The doctor has a varray of specialities and a reference of the area that works in. The trigger that i am trying to create checks if the name of the area that i will be referencing in the update is the same as the name of one of his specialities.

I have been trying different things but none of the work

Example 1.

CREATE OR REPLACE TRIGGER TRIGGER9 
BEFORE INSERT ON DOCTOR 
FOR EACH ROW
WHEN (deref(new.worksIn).name in (select m.COLUMN_VALUE.name from table (select deref(specialities) from doctor where pid = new.pid)
BEGIN
   null;
END;

Example 2.

CREATE OR REPLACE TRIGGER TRIGGER9
BEFORE INSERT ON DOCTOR
FOR EACH ROW
BEGIN
    if deref(:new.worksIn).name in (select deref(:new.specialities).name) then
       -- stuff happens
    end if
END;

Thanks in advance!

Answer 1

We cannot create constraints on object types. This is just one reason why using object types for persistence (rather than in PL/SQL programs) is deprecated.

However, it is possible to enforce uniqueness in a trigger. This one creates a SET of entries (i.e. one instance of unique values) and compares it with the actual nested table. If the counts are different the nested table has duplicate values.

The set up:

create or replace type speciality_t as object (name varchar2(30));
/

create or replace type specialities_nt as table of speciality_t;
/

create table doctors (
     doctor_id number primary key
     , works_in specialities_nt
     )
nested table works_in store as works_in_nt     ;

The trigger:

create or replace trigger ck_speciality 
    before insert or update on doctors
    for each row
declare
    dummy1 specialities_nt;
    dummy2 number;
begin
    -- all the unique values for WORKS_IN
    select  set(:new.works_in)
    into dummy1
    from dual;

    -- count diff 
    select  m - n
    into dummy2
    from ( select count(*) as m from table(:new.works_in)) t1
        cross join  ( select count(*) as n from table(dummy1)) t2;

    -- hurl if the difference is not zero
    if dummy2 != 0 then
        raise_application_error (-20042, 'duplicate speciality'); 
    end if;
end;
/

---

To be clear, I don't think using nested tables is the best way to store this data: the correct approach would be a reference table of SPECIALITY and an intersection table DOCTOR_SPECIALITY to persist which doctors practice which specialities.

However, I am intrigued to know whether anybody can come up with a more elegant solution than the above.