| The trigger still not work, Does anyone can help please [message #1777] |
Wed, 29 May 2002 02:43  |
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 or replace 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
where f_no=new.f_no
if :new.f_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: The trigger still not work, Does anyone can help please [message #1778 is a reply to message #1777] |
Wed, 29 May 2002 02:50  |
K.SREENIVASAN
Messages: 110
Registered: January 2001
Location: banglore
|
Senior Member |
|
|
SIR,
PLEASE TRY THIS ONE
Create or replace trigger totally_booked
after insert or update 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
where f_no=new.f_no;
if :new.f_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;
/
(GIVE THIS ; AS IN NECESSARY PLACE)
THAT' ALL.
K.SREENIVASAN
|
|
|
|
| Re: The trigger still not work, Does anyone can help please [message #1780 is a reply to message #1777] |
Wed, 29 May 2002 07:22 |
Paul
Messages: 164
Registered: April 1999
|
Senior Member |
|
|
First, modifications to the data being inserted or updated in the table on which the TRIGGER is placed (in your case FLIGHT) should be done in a BEFORE trigger to avoid mutating table errors.
Second, you should only raise an exception when you want to ABORT the INSERT/UPDATE which fired the trigger, but your code will raise the exception every
time an insert or update is attempted that does NOT overbook a flight, and therefore very few inserts or updates will be able to be performed.
Third the WHERE clause of your select references a column that does not exist on the AIRCRAFT table, think you must want to compare A_NO (assuming this is
the unique ID of an airplane, a fairly safe assumption given that it is the PK).
The trigger below should do what I am guessing you really want to do
Create or replace trigger totally_booked
BEFORE insert or update of f_total_takings ON flight
for each row
DECLARE
v_seat_total number(5);
BEGIN
-- get the number of seats on the plane
select a_seat_total
INTO v_seat_total
from aircraft a
where a.a_no = :new.a_no;
if :new.f_total_takings > v_seat_total then
-- flight over booked - abort the insert or update
raise_application_error(-2001,'Flight' ||:new.f_no|| 'is overbooked');
else
if :new.f_total_takings = v_seat_total then
-- flight is now booked solid - set the flag
:new.f_totally_booked := 'Y';
else
-- seats still available - do nothing
null;
end if;
end if;
END;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
