Oracle SQL - Creating trigger that accesses multiple tables

user_4410265 0
Viewed 4808

I have three tables:

table_family(
id CHAR(4) PRIMARY KEY,
name VARCHAR(30)
)

table_child(
id CHAR(4) PRIMARY KEY,
name VARCHAR(30),
family_parents_id CHAR(4) REFERENCES table_family(id)
)

table_babysit(
family_babysitter_id CHAR(4) REFERENCES table_family(id),
child_babysittee_id CHAR(4) REFERENCES table_child(id),
hours INTEGER
)

I'm trying to create a trigger before insert in table_babysit which prevents a family member from babysitting their own children. So if in table_babysit, the family_babysitter_id is matches the same family id as the child's family_parents_id, that would be illegal.

CREATE TRIGGER check_illegal_babysit
BEFORE INSERT
ON table_babysit
FOR EACH ROW
BEGIN
JOIN table_family ON table_family.id = family_babysitter_id
JOIN table_child ON table_child.id = child_babysittee_id
IF (table_family.id = table_child.family_parents_id) THEN
RAISE_APPLICATION_ERROR(-20000,'Family cannot babysit their own children');
END IF;
END;

I'm new to writing triggers and I can't seem to JOIN multiple tables in a trigger. What would be the proper way to create this trigger?

database-trigger join plsql oracle
1 Answers

Whether it's a trigger,a statement in a standard procedure, or just a standalone query to employ a JOIN it must follow the form Select ... [into ...] from table1 JOIN table2 on join_condition ... Where a trigger and procedural statement requires the INTO phrase.

In his case you can employ a CTE to create table1. However, there is a complication here in that the conditions needed for the join is actually the condition the trigger is trying prevent, but it can be made to work by reversing the logic and selecting what you do not want:

-- define trigger (with join)
create or replace trigger check_illegal_babysit
before insert
on table_babysit
for each row
declare 
   x varchar2(1);

begin
     with s as
          (select :new.family_babysitter_id sitter
                , :new.child_babysittee_id  sittee
             from dual 
          )       
     select null
       into x
       from s 
             left outer join table_family on(table_family.id = sitter)
             left outer join table_child  on(table_child.id  = sittee)
       where table_family.id = table_child.family_parents_id;             

       raise_application_error(-20000,'Family cannot babysit their own children');
 exception 
   when no_data_found then null;
end;

--- Create test Family and Child rows
insert into table_family (id, name) values('Fam1','Family1');
insert into table_family (id, name) values('Fam2','Family2');
insert into table_family (id, name) values('Fam3','Family3');

insert into table_child( id,name,family_parents_id) values('c1f1', 'Child1 of Family1', 'Fam1');
insert into table_child( id,name,family_parents_id) values('c2f1', 'Child2 of Family1', 'Fam1');
insert into table_child( id,name,family_parents_id) values('c3f1', 'Child3 of Family1', 'Fam1');
insert into table_child( id,name,family_parents_id) values('c1f2', 'Child1 of Family2', 'Fam2');
insert into table_child( id,name,family_parents_id) values('c2f2', 'Child2 of Family2', 'Fam2');    

-- Insert into babysit table to test trigger
insert into table_babysit(family_babysitter_id, child_babysittee_id)  values( 'Fam2', 'c1f1') ; -- valid
insert into table_babysit(family_babysitter_id, child_babysittee_id)  values( 'Fam3', 'c2f1') ; -- valid
insert into table_babysit(family_babysitter_id, child_babysittee_id)  values( 'Fam1', 'c3f1') ; -- invalid

I'm sure there are other JOINS that accomplish what you desire. I just can not think of one at the moment. But perhaps the easiest understand is to use 2 simple straight forward selects. So maybe try:

create or replace trigger check_illegal_babysit
    before insert
    on table_babysit
    for each row
declare 
       family_id_l  table_family.id%type;
       parents_id_l table_child.family_parents_id%type;
begin 

     select table_family.id 
       into family_id_l
       from table_family
      where id = :new.family_babysitter_id;

     select family_parents_id 
       into parents_id_l
       from table_child
      where id = :new.child_babysittee_id;

     if (family_id_l =  parents_id_l) then
        raise_application_error(-20000,'Family cannot babysit their own children');
     end if;
end;
user_76238560
Related