S.O.S. ayudente por favor.

From: "Ccccccc IiiiiiiiiNnnnnnn" <cinamuche(at)hotmail(dot)com>
To: pedrobaldovinomartin(at)yahoo(dot)com(dot)ar
Cc: pgsql-es-ayuda(at)postgresql(dot)org
Subject: S.O.S. ayudente por favor.
Date: 2006-07-12 16:04:48
Message-ID: BAY111-F24B3A82CB4DE928A6CE615BC690@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-es-ayuda

<html><div style='background-color:'><DIV class=RTE>Amigo tengo un problema, sucede que tengo una tabla y cuando le hago un update desde vfp se duplican los datos apesar q tengo un primary key, abajo t detalle todo. El Postgres q uso es la version 7.3</DIV>
<DIV class=RTE>&nbsp;</DIV>
<DIV class=RTE>Tabla : en el POSTGRESQL</DIV>
<DIV class=RTE>&nbsp;</DIV>
<DIV class=RTE>- Table: dba.sobre</DIV>
<DIV class=RTE>-- DROP TABLE dba.sobre;</DIV>
<DIV class=RTE>CREATE TABLE dba.sobre<BR>(<BR>-- Heredado:&nbsp;&nbsp; usercrea char(5),<BR>-- Heredado:&nbsp;&nbsp; feccrea timestamp,<BR>-- Heredado:&nbsp;&nbsp; userupd char(5),<BR>-- Heredado:&nbsp;&nbsp; fecupd timestamp,<BR>&nbsp; numorden int4 NOT NULL DEFAULT 0,<BR>&nbsp; numsec int4 NOT NULL DEFAULT 1,<BR>&nbsp; codexterno char(15),<BR>&nbsp; codsuborden char(6),<BR>&nbsp; fecproceso date NOT NULL,<BR>&nbsp; fecdespacho date,<BR>&nbsp; hordespacho time,<BR>&nbsp; coddespacho char(5),<BR>&nbsp; numsalidas int2 NOT NULL DEFAULT 0,<BR>&nbsp; numruc char(11),<BR>&nbsp; dscdestinatario char(60),<BR>&nbsp; dscdireccion char(60),<BR>&nbsp; dscdirecrefer char(60),<BR>&nbsp; numcuadrante int2 NOT NULL DEFAULT 0,<BR>&nbsp; codmotivo char(2),<BR>&nbsp; codtipdocumento char(3),<BR>&nbsp; codpostal char(3) NOT NULL,<BR>&nbsp; codrazon char(2),<BR>&nbsp; codmaestro char(12),<BR>&nbsp; numruta
char(6),<BR>&nbsp; swestado char(1) NOT NULL DEFAULT 'A',<BR>&nbsp; codubicacion char(2),<BR>&nbsp; numguiadigit char(6),<BR>&nbsp; codcliente char(5),<BR>&nbsp; coddescargo char(5),<BR>&nbsp; fecdescargo date,<BR>&nbsp; fecretorno date,<BR>&nbsp; ndoc char(15),<BR>&nbsp; codproducto char(8),<BR>&nbsp; fecentrega_telecall date,<BR>&nbsp; hordesde_telecall int2,<BR>&nbsp; horhasta_telecall int2,<BR>&nbsp; qpiezas int4 NOT NULL DEFAULT 0,<BR>&nbsp; CONSTRAINT pk_sobre PRIMARY KEY (numorden, numsec),<BR>&nbsp; CONSTRAINT fk_cliente FOREIGN KEY (codcliente)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES dba.cliente (codcliente)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<BR>&nbsp; CONSTRAINT fk_distrito FOREIGN KEY (codpostal)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES dba.distrito (codpostal)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE
RESTRICT,<BR>&nbsp; CONSTRAINT fk_guiadigital FOREIGN KEY (numguiadigit)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES dba.guiadigital (numguiadigit)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<BR>&nbsp; CONSTRAINT fk_hruta FOREIGN KEY (numruta)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES dba.hruta (numruta)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<BR>&nbsp; CONSTRAINT fk_orden FOREIGN KEY (numorden)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES dba.orden (numorden)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE NO ACTION ON DELETE NO ACTION,<BR>&nbsp; CONSTRAINT fk_personal_desc FOREIGN KEY (coddescargo)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES dba.personal (codpersonal)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<BR>&nbsp; CONSTRAINT fk_personal_desp FOREIGN KEY
(coddespacho)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES dba.personal (codpersonal)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<BR>&nbsp; CONSTRAINT fk_producto FOREIGN KEY (numorden, codproducto)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES dba.producto (numorden, codproducto)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<BR>&nbsp; CONSTRAINT fk_razon FOREIGN KEY (codrazon)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES dba.razon (codrazon)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<BR>&nbsp; CONSTRAINT fk_sub_orden FOREIGN KEY (numorden, codsuborden)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES dba.sub_orden (numorden, numsorden)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<BR>&nbsp; CONSTRAINT fk_tipo_documento FOREIGN KEY
(codtipdocumento)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES dba.tipo_documento (codtipdocumento)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<BR>&nbsp; CONSTRAINT chk_estado CHECK (((swestado = 'A'::bpchar) OR (swestado = 'C'::bpchar))),<BR>&nbsp; CONSTRAINT chk_numcuadrante CHECK ((numcuadrante &gt;= 0)),<BR>&nbsp; CONSTRAINT chk_numsalidas CHECK ((numsalidas &gt;= 0))<BR>) INHERITS (dba.campos_comunes) <BR>WITH OIDS;<BR>ALTER TABLE dba.sobre OWNER TO dba;</DIV>
<DIV class=RTE><BR>-- Index: dba.idx_codexterno</DIV>
<DIV class=RTE>-- DROP INDEX dba.idx_codexterno;</DIV>
<DIV class=RTE>CREATE INDEX idx_codexterno<BR>&nbsp; ON dba.sobre<BR>&nbsp; USING btree<BR>&nbsp; (codexterno)<BR>&nbsp; WHERE (codexterno IS NOT NULL);</DIV>
<DIV class=RTE>-- Index: dba.idx_primary</DIV>
<DIV class=RTE>-- DROP INDEX dba.idx_primary;</DIV>
<DIV class=RTE>CREATE UNIQUE INDEX idx_primary<BR>&nbsp; ON dba.sobre<BR>&nbsp; USING btree<BR>&nbsp; (numorden, numsec);</DIV>
<DIV class=RTE>-- Index: dba.xsobre_ruta</DIV>
<DIV class=RTE>-- DROP INDEX dba.xsobre_ruta;</DIV>
<DIV class=RTE>CREATE INDEX xsobre_ruta<BR>&nbsp; ON dba.sobre<BR>&nbsp; USING btree<BR>&nbsp; (numruta)<BR>&nbsp; WHERE (numruta IS NOT NULL);</DIV>
<DIV class=RTE>&nbsp;</DIV>
<DIV class=RTE>-- Trigger: ti_update on dba.sobre</DIV>
<DIV class=RTE>-- DROP TRIGGER ti_update ON dba.sobre;</DIV>
<DIV class=RTE>CREATE TRIGGER ti_update<BR>&nbsp; BEFORE UPDATE<BR>&nbsp; ON dba.sobre<BR>&nbsp; FOR EACH ROW<BR>&nbsp; EXECUTE PROCEDURE dba.f_actualizacion_sobre();</DIV>
<DIV class=RTE>&nbsp;</DIV>
<DIV class=RTE>*-------- Detalle del Trigger ---------------------------</DIV>
<DIV class=RTE>CREATE OR REPLACE FUNCTION dba.f_actualizacion_sobre()<BR>&nbsp; RETURNS "trigger" AS<BR>'BEGIN<BR>&nbsp;&nbsp; IF NEW.codmotivo IS NOT NULL Then&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Adiciono al Log entrada<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If (OLD.codmotivo IS NULL) OR (NEW.Codmotivo &lt;&gt; OLD.CodMotivo) Then<BR>&nbsp; --INSERT INTO dba.sobre_log Values (NEW.*) ;<BR>&nbsp; Insert Into dba.<FONT color=#0000ff>sobre_log</FONT> Values
(new.usercrea,<BR>&nbsp;&nbsp;&nbsp;new.feccrea,<BR>&nbsp;&nbsp;&nbsp;new.userupd,<BR>&nbsp;&nbsp;&nbsp;new.fecupd,<BR>&nbsp;&nbsp;&nbsp;new.numorden,<BR>&nbsp;&nbsp;&nbsp;new.numsec,<BR>&nbsp;&nbsp;&nbsp;new.codexterno,<BR>&nbsp;&nbsp;&nbsp;new.codsuborden,<BR>&nbsp;&nbsp;&nbsp;new.fecproceso,<BR>&nbsp;&nbsp;&nbsp;new.fecdespacho,<BR>&nbsp;&nbsp;&nbsp;new.hordespacho,<BR>&nbsp;&nbsp;&nbsp;new.coddespacho,<BR>&nbsp;&nbsp;&nbsp;new.numsalidas,<BR>&nbsp;&nbsp;&nbsp;new.numruc,<BR>&nbsp;&nbsp;&nbsp;new.dscdestinatario,<BR>&nbsp;&nbsp;&nbsp;new.dscdireccion,<BR>&nbsp;&nbsp;&nbsp;new.dscdirecrefer,<BR>&nbsp;&nbsp;&nbsp;new.numcuadrante,<BR>&nbsp;&nbsp;&nbsp;new.codmotivo,<BR>&nbsp;&nbsp;&nbsp;new.codtipdocumento,<BR>&nbsp;&nbsp;&nbsp;new.codpostal,<BR>&nbsp;&nbsp;&nbsp;new.codrazon,<BR>&nbsp;&nbsp;&nbsp;new.codmaestro,<BR>&nbsp;&nbsp;&nbsp;new.numruta,<BR>&nbsp;&nbsp;&nbsp;new.swestado,<BR>&n
bsp;&nbsp;&nbsp;new.codubicacion,<BR>&nbsp;&nbsp;&nbsp;new.numguiadigit,<BR>&nbsp;&nbsp;&nbsp;new.codcliente,<BR>&nbsp;&nbsp;&nbsp;new.coddescargo,<BR>&nbsp;&nbsp;&nbsp;new.fecdescargo,<BR>&nbsp;&nbsp;&nbsp;new.fecretorno) ;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If ;<BR>&nbsp;&nbsp; End if ;<BR>&nbsp;&nbsp; RETURN NEW ;<BR>END ;'<BR>&nbsp; LANGUAGE 'plpgsql' VOLATILE;<BR>GRANT EXECUTE ON FUNCTION dba.f_actualizacion_sobre() TO public;<BR>GRANT EXECUTE ON FUNCTION dba.f_actualizacion_sobre() TO postgres;<BR>GRANT EXECUTE ON FUNCTION dba.f_actualizacion_sobre() TO GROUP administradores;<BR></DIV>
<DIV class=RTE>*-----------------------------------------------------------------------------------------------------------------</DIV>
<DIV class=RTE>Aca uso la Instruccion en el VFP para actualizar, las variables precedidas con "?" son</DIV>
<DIV class=RTE>variables q almacen datos en el VFP</DIV>
<DIV class=RTE>&nbsp;</DIV>
<DIV class=RTE>lSQL = "Update dba.sobre Set coddescargo = ?pUserApp , " + ;<BR>&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" fecdescargo = ?lDTE , fecretorno = ?lFRet , " + ;<BR>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" CodMotivo = ?lMot , codrazon = ?lCRaz " + ;<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" Where NumOrden = ?lNumOrden and NumSec = ?lNumSec"</DIV>
<DIV class=RTE><BR>lHndExec = SQLEXEC(lHndConn,lSQL)<BR></DIV></div></html>

Attachment Content-Type Size
unknown_filename text/html 8.2 KB

Responses

Browse pgsql-es-ayuda by date

  From Date Subject
Next Message Diana Pachon 2006-07-12 17:14:02 informacio de postgis en español
Previous Message Héctor Santiago Rodríguez Gómez 2006-07-12 15:30:36 Tengo Problema con Crystal Report