Re: Basic Oracle Triggers

From: Stu <theevil_at_vonhumperdink.co.uk>
Date: Tue, 7 May 2002 00:28:11 +0100
Message-ID: <ab73if$fr6$1@newsg1.svr.pol.co.uk>

Hi Guys

Thanks for all your help - I take your point about testing the SQL before shoving it into a trigger... that would have been sensible :). I managed to come up with a solution based on your suggestions:

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

    bad_value exception;
    TYP airport.type%TYPE;
begin

    select COUNT(*) into TYP from airport

        where airport.code = :new.ukcode and
        airport.type = 'uk';
    if TYP < 1
        then raise bad_value;

    end if;
end;

With another similar trigger to catch foreign airports.

Cheers

Stu

"Stu" <theevil_at_vonhumperdink.co.uk> wrote in message news:ab6f75$900$1_at_news7.svr.pol.co.uk...
> 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 - 18:28:11 CDT