Re: trigger needs to check in multiple tables.

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