Re: Basic Oracle Triggers

Stu wrote:

> 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
>
>
>

You don't need a trigger, you need foreign keys! Have you actually implemented the foreign key relations you describe? If you have, you will see you need no more code - as a simple test will prove.

Frank Received on Mon May 06 2002 - 15:14:19 CDT