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