From: Michael Fuhr <mike@fuhr.org>
To: Jacobo García <jacobo.garcia@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: trigger needs to check in multiple tables.
Date: Fri, 18 Aug 2006 19:01:21 -0600
Message-ID: <20060819010121.GA86524@winnie.fuhr.org> (raw)
In-Reply-To: <5f867bd20608181017u2ccf8db7o12338bbbe66977fb@mail.gmail.com>
References: <5f867bd20608181017u2ccf8db7o12338bbbe66977fb@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, jacobo.garcia@gmail.com
Subject: Re: trigger needs to check in multiple tables.
In-Reply-To: <20060819010121.GA86524@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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