| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Basic Oracle Triggers
missing two words from sqlplus
SQL> create table airport(code varchar2,
2 name varchar2(50),
3 type char(2)
4 .
SQL> ed
Wrote file afiedt.buf
1 create table airport(code varchar2(6),
2 name varchar2(50),
3* type char(2))
SQL> /
Table created.
SQL> alter table airport add constraint pk_airport primary key(code);
Table altered.
SQL> create table fliesto(flightcode vrachar2(10),ukcode
varchar2(6),holidaycode varchar2(6))
2 .
SQL> ed
Wrote file afiedt.buf
1* create table fliesto(flightcode varchar2(10),ukcode
varchar2(6),holidaycode varchar2(6))
SQL> /
Table created.
SQL> create trigger fliesto_check
2 before insert or update of ukcode on fliesto
3 for each row
4 declare
5 bad_value exception; 6 TYP airport.type%TYPE 7 begin 8 select type into TYP from airport,fliesto 9 where airport.code = :new.ukcode; 10 if not TYP = 'uk' 11 then raise bad_value; 12 end if;
Warning: Trigger created with compilation errors.
SQL> show errors
Errors for TRIGGER FLIESTO_CHECK:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1 PLS-00103: Encountered the symbol "BEGIN" when expecting one of
the following:
:= ( ; not null range default character
SQL>
-- Niall Litchfield Oracle DBA Audit Commission UK "Stu" <theevil_at_vonhumperdink.co.uk> wrote in message news:ab6f75$900$1_at_news7.svr.pol.co.uk...Received on Mon May 06 2002 - 14:19:32 CDT
> 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
>
>
 |
 |