problem with triggers

Started by miguel angel rojas aquinoover 24 years ago3 messagesgeneral
Jump to latest
#1miguel angel rojas aquino
mrojas_aquino@mail.flashmail.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: miguel angel rojas aquino (#1)
Re: problem with triggers

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

#3miguel angel rojas aquino
mrojas_aquino@mail.flashmail.com
In reply to: Tom Lane (#2)
Re: problem with triggers

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 :)