| Problem of create trigger [message #1752] |
Sun, 26 May 2002 18:49  |
Max
Messages: 18
Registered: May 2002
|
Junior Member |
|
|
I have create the flight and booking table and the trigger as below. I want to create a trigger which can updates the attribute f_totally_booked in the flight table when every seat for the type of aircraft is booked on a particular flight.
create table flight
(f_no char(5),
a_no char(2),
f_dep_date date NOT NULL,
f_dep_city char(15) NOT NULL,
f_dest_date date NOT NULL,
f_dest_city char(15) NOT NULL,
f_totally_booked char(1) check (f_totally_booked='N' or f_totally_booked='Y'),
f_total_takings number(3) NOT NULL,
constraint flight_pk primary key(f_no),
constraint flight_fk foreign key(a_no) references aircraft(a_no) on delete cascade)
cluster booking_flight (f_no);
create table aircraft
(a_no char(2) constraint aircraft primary key,
a_type char(20) NOT NULL,
a_seat_total number(3) NOT NULL,
a_last_mainten_date date NOT NULL);
Create trigger totally_booked
after insert or update of f_total_takings ON flight
for each row
Declare
v_seat_total number
v_total_takings number
BEGIN
select a_seat_total INTO v_seat_total from aircraft
select f_total_takings INTO v_total_takings from flight
where f_no=new.f_no
if v_total_takings + 1 > v_seat_total
then update flight
set f_totally_booked = 'Y'
where f_no=new.f_no;
else
raise_application_error(-2001,'The' ||:new.f_no|| 'still avaiable');
end if;
end;
/
|
|
|
|
| Re: Problem of create trigger [message #1766 is a reply to message #1752] |
Tue, 28 May 2002 06:48  |
Paul
Messages: 164
Registered: April 1999
|
Senior Member |
|
|
Try removing "select f_total_takings INTO v_total_takings from flight" part of the trigger and changing if v_total_takings + 1 > v_seat_total
to if :new.f_total_takings + 1 > v_seat_total
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
