pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtrigger needs to check in multiple tables.
2+ messages / 2 participants
[nested] [flat]
* trigger needs to check in multiple tables.
@ 2006-08-18 17:17 Jacobo García <jacobo.garcia@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Jacobo García @ 2006-08-18 17:17 UTC (permalink / raw)
To: pgsql-sql
Hello.
I'm running a simple query inside a function that is associated with a
trigger:
SELECT tipo INTO tipocuenta FROM producto WHERE codigo_cuenta=
NEW.codigo_destino;
I am getting this error when running the code on pgadmin III
ERROR: NEW used in query that is not in a rule
QUERY: SELECT tipo FROM producto WHERE codigo_cuenta=NEW.codigo_destino
I don't know what is failing. I'm running postgresql 8.1.4 on windows. Maybe
I have to enable something?
Here is the complete code of the function, sure there will be things wrong,
but the statement failing is just next to BEGIN
CREATE OR REPLACE FUNCTION movimientosenoficina()
RETURNS BOOLEAN AS $$
DECLARE
esoficinacorrecta BOOLEAN;
esfechacorrecta BOOLEAN;
op INTEGER;
fondo INTEGER;
imp INTEGER;
tipocuenta INTEGER;
BEGIN
SELECT tipo INTO tipocuenta FROM producto WHERE codigo_cuenta=
NEW.codigo_destino;
IF (tipocuenta=71) THEN
IF (op=51) THEN
esfechacorrecta:=primerosmes();
esoficinacorrecta:=oficinacorrecta(NEW.codigo,NEW.codigo_oficina
);
ELSIF (NOT op=11 OR NOT op=12)
RAISE EXCEPTION 'Operación no permitida sobre cuentas a plazo';
END IF;
ELSIF (tipocuenta=60)
--Permiten todo tipo de operaciones
ELSIF (tipocuenta=10)
IF (op=54) THEN
SELECT saldo INTO fondo FROM producto WHERE codigo_cuenta =
NEW.destino;
SELECT cantidad INT imp FROM movimiento WHERE codigo =
NEW.codigo;
IF (fondo!=imp) THEN
RAISE EXCEPTION 'Se ha de transferir todo el saldo de la
cuenta'
ELSE
oficinacorrecta(NEW.codigo,NEW.codigo_oficina);
-- Ponemos la cuenta inactiva si retiramos los fondos
UPDATE cuenta_a_plazo SET activo=TRUE WHERE codigo_cuenta =
NEW.destino;
ENDIF;
ENDIF;
ELSE
RAISE EXCEPTION 'Tipo de cuenta no existente';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
/* Este trigger se activa una vez hemos comenzado la transaccion y hemos
insertado en movimiento_oficina
nos sirve para evitar operaciones no definidas o erroneas sobre los
distintos tipos de cuentas*/
CREATE TRIGGER validamovimientosenoficina BEFORE INSERT OR UPDATE ON CLIENTE
FOR EACH ROW EXECUTE PROCEDURE movimientosenoficina();
--
Jacobo García López de Araujo
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: trigger needs to check in multiple tables.
@ 2006-08-19 01:01 Michael Fuhr <mike@fuhr.org>
parent: Jacobo García <jacobo.garcia@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Michael Fuhr @ 2006-08-19 01:01 UTC (permalink / raw)
To: Jacobo García <jacobo.garcia@gmail.com>; +Cc: pgsql-sql
On Fri, Aug 18, 2006 at 07:17:27PM +0200, Jacobo Garca wrote:
> I'm running a simple query inside a function that is associated with a
> trigger:
>
> SELECT tipo INTO tipocuenta FROM producto WHERE codigo_cuenta=
> NEW.codigo_destino;
>
> I am getting this error when running the code on pgadmin III
>
> ERROR: NEW used in query that is not in a rule
> QUERY: SELECT tipo FROM producto WHERE codigo_cuenta=NEW.codigo_destino
Trigger functions must return TRIGGER; the function you posted
returns BOOLEAN. If you change the return type to TRIGGER then the
above error should go away. However, the function also has several
syntax errors.
> ELSIF (NOT op=11 OR NOT op=12)
The above line and a few others are missing THEN.
> SELECT cantidad INT imp FROM movimiento WHERE codigo => NEW.codigo;
INT should be INTO.
> RAISE EXCEPTION 'Se ha de transferir todo el saldo de la cuenta'
This line is missing a trailing semicolon.
> oficinacorrecta(NEW.codigo,NEW.codigo_oficina);
Use PERFORM to call a function for its effects and ignore its return
value. Or did you mean to assign the return value to a variable?
> ENDIF;
ENDIF should be END IF.
If you make the indicated changes then the function should be created
successfully. I didn't look closely at the logic, so whether it'll
actually work is another matter ;-)
--
Michael Fuhr
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-08-19 01:01 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-18 17:17 trigger needs to check in multiple tables. Jacobo García <jacobo.garcia@gmail.com>
2006-08-19 01:01 ` Michael Fuhr <mike@fuhr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox