Trigger in Oracle

Question

I create the following tables:

create table Tasks(
code varchar2(9),
name varchar2(40),
start_date date, 
end_date date,
constraint pk_code primary key (code)
);

create table secondary_Tasks(
code varchar2(9),
code_primary varchar2(9),
name varchar2(40),
start_date date, 
end_date date,
constraint pk_code2 primary key (code),
constraint fk_code foreign key (code_primary) references Tasks (code)
);

I inserted the following datas:

insert into Tasks values ('001','Task1',to_date('2010-02-15','YYYY-MM-DD'),to_date('2011-04-12','YYYY-MM-DD'));
insert into Tasks values ('002','Task2',to_date('2015-08-11','YYYY-MM-DD'),to_date('2015-09-25','YYYY-MM-DD'));
insert into Tasks values ('003','Task3',to_date('2016-05-09','YYYY-MM-DD'),null);
insert into Tasks values ('004','Task4',to_date('2014-01-23','YYYY-MM-DD'),to_date('2014-06-04','YYYY-MM-DD'));

insert into secondary_Tasks values ('s01','001','Secundary_Task1',to_date('2010-03-16','YYYY-MM-DD'),to_date('2011-05-13','YYYY-MM-DD'));
insert into secondary_Tasks values ('s02','002','Secundary_Task2',to_date('2015-09-12','YYYY-MM-DD'),to_date('2015-10-26','YYYY-MM-DD'));
insert into secondary_Tasks values ('s04','004','Secundary_Task4',to_date('2014-02-24','YYYY-MM-DD'),to_date('2014-07-05','YYYY-MM-DD'));

The question is how to make a trigger that does not allow me to add a secondary task to task '003' because It hasnt finished.

Answer 1

I'm unable to test right now, but I'd try something like

create or replace trigger secondary_tasks_bi
before insert or update on secondary_tasks for each row
declare
   v_dummy varchar2(1);
begin
   select null
     into v_dummy
     from tasks
    where code = :new.code_primary
      and end_date is null;

   raise_application_error(-20001, 'Can''t add a secondary task to task ' || :new.code_primary || ' because it hasn''t finished');

   exception
      when no_data_found then
         null;
end;
/

Note that this assumes that code_primary is never null: you probably want to add a NOT NULL clause to it.