Date: Fri, 18 Aug 2006 19:01:21 -0600
From: Michael Fuhr <mike@fuhr.org>
To: Jacobo =?iso-8859-1?Q?Garc=EDa?= <jacobo.garcia@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: trigger needs to check in multiple tables.
Message-ID: <20060819010121.GA86524@winnie.fuhr.org>
References: <5f867bd20608181017u2ccf8db7o12338bbbe66977fb@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <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