Basic Oracle Triggers

Hi

I'm a uni student with a databases lab assignment to do, and I'm having problems with triggers in sqlplus. I think what I'm trying to do is pretty simple, but our notes on triggers are terrible :) :

I've got two tables:

AIRPORT(code, name, type)
where code is the primary key. type contains either 'uk' or 'hl' and this is enforced by a check in the CREATE TABLE statement for AIRPORT.

FLIESTO(flightcode, ukcode, holidaycode) where flightcode is the primary key. This table represents a combination of airports for a holiday package... one in the uk (ukcode) and one in a foreign country (holidaycode). ukcode and holidaycode are foreign keys into AIRPORT.code.

When a new tuple is added to the FLIESTO table, I want to check that the airport code entered in ukcode is in the UK and the code entered in holidaycode is in a foreign country. In other words, I want to use a trigger to look up the new ukcode in the AIRPORT table and check that the type is 'uk' (and likewise for holidaycode). My lamentable attempt to write a trigger for ukcode looks like this:

create trigger fliesto_check
before insert or update of ukcode on fliesto for each row
declare

    bad_value exception;
    TYP airport.type%TYPE
begin

    select type into TYP from airport,fliesto

        where airport.code = :new.ukcode;     if not TYP = 'uk'

        then raise bad_value;
    end if;
end;

This is quite simply bodged together from several sources, and doesn't work. When I type it into sqlplus and try running it, I'm told it compiles with errors (although it helpfully doesn't tell me what the errors were!). It then just doesn't work when I attempt to modify the contents of the FLIESTO table.

Can anyone tell me how to write this trigger, or point me towards a good website with a very similar example? Sorry if this is kinda basic, but I'm not sure where I'm going wrong.

Cheers

Stu Received on Mon May 06 2002 - 12:40:46 CDT