Thread: Trigger problem

Trigger problem

From
"Alejandro D. Burne"
Date:
Hi, I'll be trying to write a my first trigger which fire when an user
makes an insert into a table; this insert records in others tables.
But when I try to insert a record in trigger's table generate an error:
 - Syntax error near "into" in char 9. -

This is the trigger.

CREATE OR REPLACE FUNCTION socios_insert()
  RETURNS "trigger" AS
$BODY$
    BEGIN
-- Creo el domicilio mltiple
    INSERT INTO DomiciliosMultiples (CodigoProfesion,
MatriculaProfesional, NumeroSecuencia,
      CodigoProfesionCliSanHosp, MatriculaProfesionalCliSanHosp, Calle,
Numero, Piso, Dto, Otros,
      Telefono, CodigoLocalidad, HorariosAtencion, NombreInstitucion,
HabilitacionAMR,
      HabilitacionColegio, ExtensionHabilitacion, Titular,
CodigoSeccional, CodigoZona) VALUES
      (NEW.CodigoProfesion, NEW.MatriculaProfesional, 1, 0, 0,
NEW.CalleConsultorio,
      NEW.NumeroConsultorio, NEW.PisoConsultorio, NEW.DtoConsultorio,
NEW.OtrosConsultorio,
      NEW.TelefonoConsultorio, NEW.CodigoLocalidadConsultorio, '', '',
FALSE, FALSE, FALSE, FALSE, 0, 99);

-- Creo las redes por defecto
    INSERT INTO RedDomicilioMultiple (CodigoDomicilioMultiple, CodigoRed,
FechaInicioVigencia)
      VALUES (CURRVAL('domiciliosmultiples_codigodomiciliomultiple_seq'),
2, NEW.FechaIngresoCTM);

    INSERT INTO RedDomicilioMultiple (CodigoDomicilioMultiple, CodigoRed,
FechaInicioVigencia)
      VALUES (CURRVAL('domiciliosmultiples_codigodomiciliomultiple_seq'),
13, NEW.FechaIngresoCTM);

    INSERT INTO RedDomicilioMultiple (CodigoDomicilioMultiple, CodigoRed,
FechaInicioVigencia)
      VALUES (CURRVAL('domiciliosmultiples_codigodomiciliomultiple_seq'),
99, NEW.FechaIngresoCTM);
    END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER tsocios_insert_after
  AFTER INSERT
  ON socios
  FOR EACH ROW
  EXECUTE PROCEDURE socios_insert();

Thanks, Alejandro

Re: Trigger problem

From
Michael Fuhr
Date:
On Wed, Jul 20, 2005 at 03:00:42PM -0300, Alejandro D. Burne wrote:
> Hi, I'll be trying to write a my first trigger which fire when an user
> makes an insert into a table; this insert records in others tables.
> But when I try to insert a record in trigger's table generate an error:
>  - Syntax error near "into" in char 9. -

The INSERT statement itself might be at fault instead of the trigger
function.  Please post the INSERT statement and the complete, exact
error message (there are probably multiple lines).  If that doesn't
tell us what's wrong, then we'll need to see a self-contained
example, i.e., all SQL statements that somebody could execute in
an empty database to reproduce the problem.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: Trigger problem

From
"Alejandro D. Burne"
Date:
Well, after hours to debug this issue, I found the problem.
There is a misspelled word, thanks and sorry for waste your time.

Alejandro

2005/7/21, Alejandro D. Burne <alejandro.dburne@gmail.com>:
> Thanks Michael for your reply, I've attached the db structure and some
> data to add, this is the command that fire the trigger.
>
> insert into socios (codigoprofesion, matriculaprofesional, codigoctm,
> nombreprofesional, codigotipodocumento,
> numerodocumento, calleparticular, numeroparticular, pisoparticular,
> dtoparticular, otrosparticular,
> codigolocalidadparticular, telefonoparticular, calleconsultorio,
> numeroconsultorio, pisoconsultorio,
> dtoconsultorio, otrosconsultorio, codigolocalidadconsultorio,
> telefonoconsultorio, codigocobrador,
> codigolocalidadcobranza, codigozona, sexo, codigomotivobajaservsoc,
> fechaingresoctm, codigomotivobajactm,
> codigonacionalidad, fechaegreso, codigocondicioniva, cuit,
> codigoganancia, ingresosbrutos,
> nrocajajubilacion, nroartedecurar, codigouniversidad,
> codigoespecialidad, codigoespecialidad2,
> codigoformacobro, codigobanco, codigosucursal, numerocuentabanco,
> codigotiposociedad, numerotarjeta,
> codigogastoadministrativo, email, numeroregistrosss,
> nombreprofesionalcheque) VALUES
> (1, 1, 1, 'TEST', 1, 123456, '', '', '', '', '', 20000, '', '', '',
> '', '', '', 20000, '', 1,
> 20000, 1, 'M', 0, CURRENT_DATE, 0, 1, CURRENT_DATE, 1, '', 1, '', 0,
> 0, 1, 0, 0, 1, 0, 0, '', 0, 0,
> 1, '', 0, '');
>
> Error message (in spanish, sorry):
> ERROR:  error de sintaxis en o cerca de «INTO» at character 9
> QUERY:  SELECT  INTO  NroRegistros COUNT(CodigoDomicilioMultiple) FROM
> DomiciliosMultiples WHERE CodigoProfesion= $1  AND
> MatriculaProfesional= $2
> CONTEXT:  PL/pgSQL function "socio_update" line 21 at SQL statement
>
> I don't know why fires socio_update trigger (seems to be the problem),
> I just insert a record on socios, there is no update command on socios
> on socio_insert trigger.
>
> Thanks again Alejandro
>
>
>
> 2005/7/20, Michael Fuhr <mike@fuhr.org>:
> > On Wed, Jul 20, 2005 at 03:00:42PM -0300, Alejandro D. Burne wrote:
> > > Hi, I'll be trying to write a my first trigger which fire when an user
> > > makes an insert into a table; this insert records in others tables.
> > > But when I try to insert a record in trigger's table generate an error:
> > >  - Syntax error near "into" in char 9. -
> >
> > The INSERT statement itself might be at fault instead of the trigger
> > function.  Please post the INSERT statement and the complete, exact
> > error message (there are probably multiple lines).  If that doesn't
> > tell us what's wrong, then we'll need to see a self-contained
> > example, i.e., all SQL statements that somebody could execute in
> > an empty database to reproduce the problem.
> >
> > --
> > Michael Fuhr
> > http://www.fuhr.org/~mfuhr/
> >
>
>
>