problem with triggers

From: miguel angel rojas aquino <mrojas_aquino(at)mail(dot)flashmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: problem with triggers
Date: 2001-07-31 15:29:48
Message-ID: 3B66CEEC.F8F3D495@mail.flashmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message tankgirl 2001-07-31 16:14:33 Asking for some PL/pgSQL Tips
Previous Message wsheldah 2001-07-31 15:00:23 Re: ODBC read/write permission in MS Access