Trigger problem

Started by Alejandro D. Burneover 20 years ago3 messagesgeneral

alejandro.dburne@gmail.com

over 20 years ago

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

Michael Fuhr

mike@fuhr.org

over 20 years ago

In reply to: Alejandro D. Burne (#1)

Re: Trigger problem

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/

Alejandro D. Burne

alejandro.dburne@gmail.com

over 20 years ago

In reply to: Alejandro D. Burne (#1)

Re: Trigger problem

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>:

Show quoted text

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/

Import Notes

Reply to msg id not found: 8398dc6d050721102358752dc9@mail.gmail.com