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?

Georgia

I do not know oracle, but ill take a shot here. Looks like the trigger is trying to SELECT data as a Result Set, which is not legal in a trigger. What are you trying to do? – Luminal

With all of your questions, it might help if you post an outline of your assignment so we can see what you're supposed to be trying to achieve. I'd also suggest you investigate the meaning of the error messages and refer to the documentation before asking questions about assignments, or you're unlikely to really learn much from the exercise; and make sure you aren't posting the same question in slightly different forms as there seems to be some overlap... – Soupspoon

Answer 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.

Longlongan

Good point. Although from the OP's other questions I'm not sure that's actually what he's supposed to be doing anyway. – Soupspoon

Answer 2

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.

Soupspoon

Answer 3

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;

Malapropos