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

Frank is correct that a trigger is not required. But as I teach Oracle I must confess that often I give problems where I ask for a result that could easily be accomplished by ten other means. So assuming you have been instructed to write a trigger consider this:

SELECT COUNT(*)
INTO variable
FROM airport;

IF variable > 0 THEN

Part of your problem, I suspect, is a mutating trigger error from querying the table on which you have the trigger. Though why you included fliesto in the query in your trigger is something you might wish to consider. Is it necessary? Seems to me that since you have no WHERE clause joining the two tables you have a marvelous example of a Cartesian product.

It is a good habit to get into to never put a piece of SQL into a procedure, package, trigger, or function that you haven't tested at the SQL*Plus command line to see what it will do.

Daniel Morgan Received on Mon May 06 2002 - 14:29:35 CDT