CODEWITHSUNDEEP
sqloracle-databasetriggersora-00933pls-00428
andandandand
andandandand

Reputation: 22270

Error(2,7): PLS-00428: an INTO clause is expected in this SELECT statement

I'm trying to create this trigger and getting the following compiler errors:

create or replace
TRIGGER RESTAR_PLAZAS
AFTER INSERT ON PLAN_VUELO
BEGIN
SELECT F.NRO_VUELO, M.CAPACIDAD, M.CAPACIDAD - COALESCE((
SELECT count(*) FROM PLAN_VUELO P
WHERE P.NRO_VUELO = F.NRO_VUELO
       ), 0) as PLAZAS_DISPONIBLES
FROM VUELO F
      INNER JOIN MODELO M ON M.ID = F.CODIGO_AVION; 
END RESTAR_PLAZAS;


Error(2,7): PL/SQL: SQL Statement ignored
Error(8,5): PL/SQL: ORA-00933: SQL command not properly ended
Error(8,27): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:     begin case declare end exception exit for goto if loop mod    null pragma raise return select update while with    <an identifier> <a double-quoted delimited-identifier>    <a bind variable> << close current delete fetch lock insert    open rollback savepoint set sql execute commit forall merge    pipe 
Error(2,1): PLS-00428: an INTO clause is expected in this SELECT statement

What's wrong with this trigger?

Upvotes: 2

Views: 12837

Answers (3)

fabiopagoti
fabiopagoti

Reputation: 1541

Just add the into clause according to the result type, one example:

declare
  my_result VUELO%rowtype;
begin
  select v.* into my_result from VUELO v where id = '1';
end;

Upvotes: 1

Gary Myers
Gary Myers

Reputation: 35401

You won't be allowed to

SELECT count(*) FROM PLAN_VUELO

in a trigger on PLAN_VUELO

Don't use a trigger. Use a stored procedure.

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191570

Inside a PL/SQL block you have to SELECT ... INTO something. I had an example of this in an answer to one of your questions yesterday. In this case you may want to select into a local variable, and use the result to then update another table.

But it looks like you're probably going to get lots of results back because you haven't restricted to the value you're interested in; the WHERE clauses don't filter on any of the inserted row's :NEW values. That will cause an ORA-02112. You need to make sure your select will return exactly one row, or look at cursors if you actually want multiple rows.

Upvotes: 1

Related Questions