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

Question

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?

Solution 1

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.

Solution 2

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;

Solution 3

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.