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?

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? — The Scrum Meister, Jan 17 '11 at 06:30
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... — Alex Poole, Jan 17 '11 at 07:49

score 2 · Answer 1 · answered Jan 17 '11 at 08:48

2

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.

answered Jan 17 '11 at 08:48

Gary Myers

34,963
3
49
74

1

Good point. Although from the OP's other questions I'm not sure that's actually what he's supposed to be doing anyway. – Alex Poole Jan 17 '11 at 11:02

score 1 · Answer 2 · answered Sep 24 '12 at 18:21

1

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;

answered Sep 24 '12 at 18:21

fabiopagoti

1,467
14
31

score 1 · Answer 3 · answered Jan 17 '11 at 07:49

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.

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

3 Answers3

Linked