-1

I am trying to write a SQL trigger for any insert on the relation Section and it should ensure that the time slot id value that is being inserted is valid.

The Section relation

Thanks in advance!

APC
  • 144,005
  • 19
  • 170
  • 281
sfk
  • 50
  • 5
  • I wanted to know how to write such a trigger. I am using Oracle. – sfk Dec 11 '18 at 11:11
  • 1
    Please don't use images to post important details like table structure or sample data. Many people are behind firewalls which prevent access to image dumps. Also it is extremely inconvenient when it comes to recreating problems and ensuring suggested solutions compile and work. – APC Dec 11 '18 at 13:13

1 Answers1

1

Let's start by stating that using triggers to enforce relational integrity instead of foreign key constraints is worst practice. RI triggers are slow, they don't scale well, they don't work in multi-user environments, they provoke needless head-scratching amongst the poor blighters who have to maintain the code.

So, this is what a functioning worst practice trigger would look like:

create or replace trigger section_timeslot_trg 
before insert or update on section
for each row
declare
    l_id timeslot.timeslot_id%type;
begin
    select ts.timeslot_id
    into l_id
    from timeslot ts
    where ts.timeslot_id = :new.timeslot_id;
exception
    when no_data_found then
        raise_application_error(-20999, 'Not a valid TIMESLOT_ID: '||:new.timeslot_id);
end;

Remember that in the absence of a foreign key constraint there would need to be a reciprocal trigger on TIMESLOT to prevent updates and deletes of TIMESLOT_IDs which are used in SECTION.

APC
  • 144,005
  • 19
  • 170
  • 281