Thread: problem with triggers

hi, i'm trying the following procedure/triger to obtain values for some
fields in the newly inserted records:
------------------------------------------------------------------------

-- procedimiento y trigger para obtener los precios
-- a pagar en base a las opciones de curso, turno, semestre
-- y sistema elegidos

-- eliminamos versiones anteriores

DROP TRIGGER trigger_fichas ON fichas;
DROP FUNCTION trigger_ins_act_fichas();

CREATE FUNCTION trigger_ins_act_fichas()
RETURNS opaque
AS '
 DECLARE
  registro RECORD;
 BEGIN

 IF NEW.inscripcion = 0 THEN
  SELECT * INTO registro
  FROM precios
  WHERE NEW.curso = registro.curso AND
        NEW.sistema = registro.sistema AND
        NEW.turno = registro.turno AND
        NEW.semestre = registro.semestre;



  IF NOT FOUND THEN
   RAISE EXCEPTION ''No hay curso registrado.'';
  ELSE
   NEW.curso := registro.curso;
         NEW.sistema := registro.sistema;
         NEW.turno := registro.turno;
         NEW.semestre := registro.semestre;
  END IF;
 END IF;

 RETURN NEW;
    END;'
LANGUAGE 'plpgsql';

CREATE TRIGGER trigger_fichas
BEFORE INSERT OR UPDATE
ON fichas
FOR EACH ROW
------------------------------------------------------------------------

but every time i insert a new record, it says something like:

"ERROR record registro not assigned yet"

it happens inserting in psql and from a java app, some ideas about
what's wrong :(

thanks in advance

miguel angel rojas aquino <mrojas_aquino@mail.flashmail.com> writes:
>  DECLARE
>   registro RECORD;
>  BEGIN

>  IF NEW.inscripcion = 0 THEN
>   SELECT * INTO registro
>   FROM precios
>   WHERE NEW.curso = registro.curso AND
>         NEW.sistema = registro.sistema AND
>         NEW.turno = registro.turno AND
>         NEW.semestre = registro.semestre;

> but every time i insert a new record, it says something like:
> "ERROR record registro not assigned yet"

I'm betting that you meant to refer to precios, not registro,
in the WHERE clause.

            regards, tom lane

Tom Lane escribió:

> I'm betting that you meant to refer to precios, not registro,
> in the WHERE clause.
>
>                         regards, tom lane
>

aaaaaaaaaaaarrrrrggggghhhhhhhhh!! guess it's no good trying to work from
9am to 2am continuously

:)

thanks, i promise to look before sending :)