How to check attributes of a ref column in the table that is being updated - Oracle

Question

I'm very new to Oracle and struggling with REF's.

I have an employee type with the following structure:

CREATE TYPE person_type AS OBJECT(
    address at_address_type,
    name at_name_type,
    phones at_nested_phone,
    ni_num VARCHAR2(5)
)NOT FINAL;
/   

CREATE TYPE employee_type UNDER at_person_type(
    emp_id NUMBER,
    supervisor_r REF at_employee_type,  -- NOTE supervisor is a reference to the same type
    job_r REF at_job_type,
    branch_r REF at_branch_type,
    join_date DATE
)FINAL;
/

Then I create a table from employee_type:

CREATE TABLE at_employee_table of at_employee_type(
    emp_id PRIMARY KEY,
    
    CONSTRAINT check_emp_address
        CHECK (
            address.street IS NOT NULL
            AND
            address.city IS NOT NULL
            AND
            address.postcode IS NOT NULL
        ),
    CONSTRAINT check_emp_name
        CHECK (
            name.title IS NOT NULL
            AND     
            name.firstname IS NOT NULL
            AND
            name.surname IS NOT NULL
        ),
    CONSTRAINT check_emp_ni CHECK (ni_num IS NOT NULL),
    CONSTRAINT unique_emp_ni UNIQUE (ni_num),
    CONSTRAINT check_emp_job CHECK (job_r IS NOT NULL),
    CONSTRAINT check_emp_branch CHECK (branch_r IS NOT NULL),
    CONSTRAINT check_emp_join CHECK (join_date IS NOT NULL)
)NESTED TABLE phones STORE AS at_nested_emp_phones_storage;
/

What I want to be able to do is is check that the supervisor_r meets the following conditions:

supervisor_r.job_r.position IN ('head','manager','team leader') 
AND
supervisor_r.branch_r.branch_code = :NEW.branch_r.branch_code

However, I can't seem to do this as a constraint as constraints don't allow DEREF, and I can't seem to do this with a trigger as triggers don't allow navigation through REF or to read or modify the table which is currently being modified. As I'm new to oracle, I feel there may be something I can do which I haven't thought of.

Note I require the supervisor to be stored as a REF (due to other reasons).

Does anyone have any solutions? I've been pulling my hair out over this one.

I have tried constraints, and I have tried triggers as explained in the details.

I have tried to rethink my design, but I require REF's to be used for supervisor_r, and struggling to think of another design where I can navigate through supervisors (and their supervisors), still using REF whilst also enforcing the conditions mentioned.

@Alex Hi Alex, yes it does compile, however when it is called and :new.supervisor_r is not null, it produces this error:

Error starting at line : 335 in command -
INSERT INTO at_employee_table
SELECT at_employee_type(
    at_address_type(
        'Adam', -- street
        'Edinburgh', -- city
        'EH1 6EA' -- postcode
    ),
    at_name_type(
        'Mr', -- title
        'Jack', -- firstname
        'Smith' -- surname
    ),
    at_nested_phone(
        at_phone_type(
            'home', -- purpose
            '01311112223' -- num
        ),
        at_phone_type(
            'mobile', -- purpose
            '0781209890' -- num
        )
    ),
    'NI810', -- NI num
    804, -- emp ID
    REF(s), -- supervisor
    REF(j), -- job
    REF(b), -- branch
    TO_DATE('05-Feb-18','DD-Mon-YY') -- join date
)
FROM at_job_table j, at_branch_table b, at_employee_table s
WHERE j.position = 'team leader'
AND b.branch_code = 908
AND s.emp_id = 101
Error at Command Line : 335 Column : 13
Error report -
SQL Error: ORA-04091: table SYSTEM.AT_EMPLOYEE_TABLE is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.AT_EMPLOYEE_TRIG", line 6
ORA-04088: error during execution of trigger 'SYSTEM.AT_EMPLOYEE_TRIG'
04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
*Cause:    A trigger (or a user defined plsql function that is referenced in
       this statement) attempted to look at (or modify) a table that was
       in the middle of being modified by the statement which fired it.
*Action:   Rewrite the trigger (or function) so it does not read that table.

It seems that Oracle does not allow attributes of references to the same table that is being modified be read, and I'm not sure how to get around it and continue to use a REF to the same table for supervisor_r

Answer 1

Expanding on @Alex Poole's answer, the secondary problem you are having is using INSERT INTO ... SELECT and selecting from the table you are trying to insert into.

If you just want to insert a single row then use INSERT INTO ... VALUES ... and obtain the references using sub-queries (rather than joins).

Given the setup using @Alex Poole's trigger and adding the other necessary tables:

create type at_address_type as object(street varchar2(10), city varchar2(10), postcode varchar2(8))
/

create type at_name_type as object(title varchar2(5), firstname varchar2(10),surname varchar2(10))
/

create type at_phone_type as object (x varchar2(15))
/

create type at_nested_phone as table of at_phone_type
/

create type at_job_type as object (position varchar2(10))
/

create type at_branch_type as object (branch_code number)
/

CREATE TYPE at_person_type AS OBJECT(
    address at_address_type,
    name at_name_type,
    phones at_nested_phone,
    ni_num VARCHAR2(5)
)NOT FINAL;
/   

CREATE TYPE at_employee_type UNDER at_person_type(
    emp_id NUMBER,
    supervisor_r REF at_employee_type,  -- NOTE supervisor is a reference to the same type
    job_r REF at_job_type,
    branch_r REF at_branch_type,
    join_date DATE
)FINAL;
/

CREATE TABLE at_employee_table of at_employee_type(
    emp_id PRIMARY KEY,
    
    CONSTRAINT check_emp_address
        CHECK (
            address.street IS NOT NULL
            AND
            address.city IS NOT NULL
            AND
            address.postcode IS NOT NULL
        ),
    CONSTRAINT check_emp_name
        CHECK (
            name.title IS NOT NULL
            AND     
            name.firstname IS NOT NULL
            AND
            name.surname IS NOT NULL
        ),
    CONSTRAINT check_emp_ni CHECK (ni_num IS NOT NULL),
    CONSTRAINT unique_emp_ni UNIQUE (ni_num),
    CONSTRAINT check_emp_job CHECK (job_r IS NOT NULL),
    CONSTRAINT check_emp_branch CHECK (branch_r IS NOT NULL),
    CONSTRAINT check_emp_join CHECK (join_date IS NOT NULL)
)NESTED TABLE phones STORE AS at_nested_emp_phones_storage
/

CREATE TRIGGER at_employee_trig
BEFORE INSERT ON at_employee_table
FOR EACH ROW
DECLARE
  supervisor_job at_job_type;
  supervisor_branch at_branch_type;
  branch at_branch_type;
BEGIN
  SELECT DEREF(:new.branch_r),
         DEREF(DEREF(:new.supervisor_r).job_r),
         DEREF(DEREF(:new.supervisor_r).branch_r)
  INTO   branch,
         supervisor_job,
         supervisor_branch
  FROM   dual;

  IF supervisor_job.position NOT IN ('head','manager','team leader')  
  OR supervisor_branch.branch_code != branch.branch_code
  THEN
    RAISE_APPLICATION_ERROR(-20001, 'Invalid supervisor');
  END IF;
END;
/

CREATE TABLE branches OF at_branch_type;

INSERT INTO branches VALUES (at_branch_type(1));

CREATE TABLE jobs OF at_job_type;

INSERT INTO jobs VALUES (at_job_type('head'));

INSERT INTO jobs VALUES (at_job_type('X'));

Then you can insert a supervisor:

INSERT INTO at_employee_table (
  emp_Id,
  address,
  name,
  phones,
  ni_num,
  supervisor_r,
  job_r,
  branch_r,
  join_date
) VALUES (
  1,
  at_address_type('street', 'city', 'postcode'),
  at_name_type('t1', 'fname1', 'lname1'),
  at_nested_phone(),
  '00001',
  NULL,
  (SELECT REF(j) FROM jobs j WHERE position = 'head'),
  (SELECT REF(b) FROM branches b WHERE branch_code = 1),
  DATE '1970-01-01'
);

And then the employee managed by the supervisor:

INSERT INTO at_employee_table (
  emp_Id,
  address,
  name,
  phones,
  ni_num,
  supervisor_r,
  job_r,
  branch_r,
  join_date
) VALUES (
  2,
  at_address_type('street', 'city', 'postcode'),
  at_name_type('t2', 'fname2', 'lname2'),
  at_nested_phone(),
  '00002',
  (SELECT REF(e) FROM at_employee_table e WHERE emp_id = 1),
  (SELECT REF(j) FROM jobs j WHERE position = 'X'),
  (SELECT REF(b) FROM branches b WHERE branch_code = 1),
  DATE '1970-01-01'
);

But if you try to insert an employee with an invalid manager:

INSERT INTO at_employee_table (
  emp_Id,
  address,
  name,
  phones,
  ni_num,
  supervisor_r,
  job_r,
  branch_r,
  join_date
) VALUES (
  3,
  at_address_type('street', 'city', 'postcode'),
  at_name_type('t3', 'fname3', 'lname3'),
  at_nested_phone(),
  '00003',
  (SELECT REF(e) FROM at_employee_table e WHERE emp_id = 2),
  (SELECT REF(j) FROM jobs j WHERE position = 'X'),
  (SELECT REF(b) FROM branches b WHERE branch_code = 1),
  DATE '1970-01-01'
);

Then you get the exception:

ORA-20001: Invalid supervisor
ORA-06512: at "FIDDLE_HYWEWLWKRFTOFCGCZRUO.AT_EMPLOYEE_TRIG", line 17
ORA-04088: error during execution of trigger 'FIDDLE_HYWEWLWKRFTOFCGCZRUO.AT_EMPLOYEE_TRIG'

fiddle

Answer 2

I don't think you can do this with constraints, but you can with triggers, if you deref in a SQL context, something like:

CREATE TRIGGER at_employee_trig
BEFORE INSERT ON at_employee_table
FOR EACH ROW
DECLARE
  supervisor_job at_job_type;
  supervisor_branch at_branch_type;
  branch at_branch_type;
BEGIN
  SELECT DEREF(:new.branch_r),
    DEREF(DEREF(:new.supervisor_r).job_r),
    DEREF(DEREF(:new.supervisor_r).branch_r)
  INTO branch, supervisor_job, supervisor_branch
  FROM dual;

  IF supervisor_job.position NOT IN ('head','manager','team leader')  
  OR supervisor_branch.branch_code != branch.branch_code
  THEN
    RAISE_APPLICATION_ERROR(-20001, 'Invalid supervisor');
  END IF;
END;
/

fiddle showing this at least compiles; I haven't created all of the tables and data needed to test it actually behaves as you want but it looks like it should...

---

As @MT0 pointed out, your insert doesn't work with the select syntax, but does with values, e.g.:

INSERT INTO at_employee_table (address, name, phones, ni_num, emp_id, supervisor_r, job_r, branch_r, join_date)
VALUES (
    at_address_type(
        'Adam', -- street
        'Edinburgh', -- city
        'EH1 6EA' -- postcode
    ),
    at_name_type(
        'Mr', -- title
        'Jack', -- firstname
        'Smith' -- surname
    ),
    at_nested_phone(
        at_phone_type(
            'home', -- purpose
            '01311112223' -- num
        ),
        at_phone_type(
            'mobile', -- purpose
            '0781209890' -- num
        )
    ),
    'NI810', -- NI num
    804, -- emp ID
    (SELECT REF(s) FROM at_employee_table s WHERE s.emp_id = 101),
    (SELECT REF(j) FROM at_job_table j WHERE j.position = 'team leader'),
    (SELECT REF(b) FROM at_branch_table b WHERE b.branch_code = 908),
    TO_DATE('05-Feb-18','DD-Mon-YY') -- join date
);

fiddle