unable to store the value of column in variable, SQL TRIGGERS-CodePudding

The booked_tickets table that I have created to store the info of the tickets

CREATE TABLE BOOKED_TICKETS( 
PNR_NO  NUMBER PRIMARY KEY, 
USER_ID  NUMBER,
TRAIN_NO  NUMBER,
SOURCE   VARCHAR2(50),  
DESTINATION  VARCHAR2(50),  
DATE_OF_ARRIV  DATE,
DATE_OF_BOOKING  DATE,
STATUS   VARCHAR2(50),
NO_OF_SEATS NUMBER,
BOOKED_CLASS  VARCHAR2(50),
SEAT_NO   NUMBER);

The Available_seats table to store the info of the seats available for each of the available class.

AVAILABLE_SEATS:
CREATE TABLE AVAILABLE_SEATS(
TRAIN_NO  NUMBER PRIMARY KEY,
SLEEPER_SEATS   NUMBER,
ONE_TIER_SEATS  NUMBER,
TWO_TIER_SEATS  NUMBER,
THREE_TIER_SEATS NUMBER,
GENERAL_SEATS  NUMBER);

This is the Query where am facing issue to store the value of avail seats into variable.

create or replace trigger status_tgr
before insert on booked_tickets
for each row
follows tkt_capitalize_tgr
declare
availSeats int;
begin
select availSeats = sleeper_seats from AVAILABLE_SEATS where train_no = :new.train_no;
update AVAILABLE_SEATS set sleeper_seats = availSeats - (:new.no_of_seats) where train_no = (:new.train_no);
end;
/

Error

Error at line 4: PL/SQL: SQL Statement ignored

2. before insert on booked_tickets
3. for each row
4. follows tkt_capitalize_tgr
5. declare
6. availSeats int;

CodePudding user response：

If you are trying to select a value into a local variable, the syntax would be

select sleeper_seats 
  into availSeats
  from AVAILABLE_SEATS 
 where train_no = :new.train_no;

However there appears to be no point to having that local variable or doing that select into in the first place because the subsequent update can just use the sleeper_seats column given the logic in your trigger

update AVAILABLE_SEATS 
   set sleeper_seats = sleeper_seats - :new.no_of_seats
 where train_no = :new.train_no;

My guess, however, is that you would actually want your trigger to update different columns in available_seats based on the :new.booked_class which you aren't showing.

CodePudding user response：

Check you tkt_capitalize_tgr trigger there might be some issue in that.