PostgesSQL 9.2 -删除主表中的记录
提问于 2013-05-03 07:55:07
首先，我为我糟糕的英语道歉。
我正在创建一个使用PostgreSQL 9.2的新应用程序。我正在尝试使用火鸟中使用的相同“逻辑”，但显然在PostgreSQL上不起作用。
我有一个名为"Albaran“的主表和其他名为"AlbaMov”的明细表。我已经定义了一些具有相应职责的触发器，当您修改明细表中的记录时，这些触发器会更新主表。除了我想要删除主表中的一条记录之外，一切都运行得很好。
当您删除Master表中的记录时，会从Detail中删除所有记录，并且我会将Master表中的字段"Total“更新为0，但不会删除Master表记录。如果我从主表中删除记录，则明细表中没有记录的记录将被顺利删除。
我一直在测试，发现问题出在对主表的更新中，这个更新是在我调用的CalculoAlbaranVenta函数中完成的。
同样的系统在Firebird中也能完美地工作。
这个函数返回一个% ROWTYPE类型的变量，我用它来更新PHP屏幕。
在这里，我留下了带有触发器和函数的表的定义。
问题出在哪里？
提前致以问候和感谢。
CREATE OR REPLACE FUNCTION public."CalculoAlbaranVenta"
(
IN "cSerie" public."Serie",
IN "nNumeroDoc" public."NumeroDocumento"
)
RETURNS SETOF public."Totales" AS
$$
declare nBasImp "Importes";
declare nIva "Importes";
declare nRE "Importes";
declare nTotalBase "Importes";
declare nTotalIVA "Importes";
declare nTotalRE "Importes";
declare nTotalDtoBase "Importes";
declare nTotalDtoResto "Importes";
declare nTotalDtos "Importes";
declare nTotalLinea "Importes";
declare rRow RECORD;
declare rTotales "Totales"%ROWTYPE;
begin
nBasImp := 0;
nIva := 0;
nRE := 0;
nTotalBase := 0;
nTotalIVA := 0;
nTotalRE := 0;
nTotalDtoBase := 0;
nTotalDtoResto := 0;
nTotalDtos := 0;
nTotalLinea := 0;
FOR rRow IN SELECT "TotalUnidades",
"Precio",
"PorcentajeIVA",
"PorcentajeRE",
"DescuentoBase",
"DescuentoResto"
FROM "AlbaMov"
WHERE ("Serie" = "cSerie") AND ("NumeroDoc" = "nNumeroDoc") AND
("Referencia" IS NOT NULL)
LOOP
nTotalLinea := Round((rRow."TotalUnidades" * rRow."Precio")::numeric, 3);
nTotalDtoBase := Round((nTotalLinea * (rRow."DescuentoBase" / 100))::numeric, 3);
nTotalLinea := nTotalLinea - nTotalDtoBase;
nTotalDtoResto := Round((nTotalLinea * (rRow."DescuentoResto" / 100))::numeric, 3);
nTotalLinea := nTotalLinea - nTotalDtoResto;
nTotalDtos := nTotalDtos + nTotalDtoBase + nTotalDtoResto;
nBasImp := Round(nTotalLinea::numeric, 2);
nTotalBase := nTotalBase + nBasImp;
nTotalIVA := nTotalIVA + (nBasImp * rRow."PorcentajeIVA" / 100);
nTotalRE := nTotalRE + (nBasImp * rRow."PorcentajeRE" / 100);
END LOOP;
nTotalIVA := Round(nTotalIVA::numeric, 2);
nTotalRE := Round(nTotalRE::numeric, 2);
nTotalDtos := Round(nTotalDtos::numeric, 2);
UPDATE "Albaran"
SET "BaseImponible" = nTotalBase,
"TotalDescuentos" = nTotalDtos,
"IVA" = nTotalIVA,
"RE" = nTotalRE,
"Total" = nTotalBase + nTotalIVA + nTotalRE
WHERE ("Serie" = "cSerie") AND ("NumeroDoc" = "nNumeroDoc");
rTotales."TotalDescuentos" := nTotalDtos;
rTotales."BaseImponible" := nTotalBase;
rTotales."TotalIVA" := nTotalIVA;
rTotales."TotalRE" := nTotalRE;
rTotales."Total" := nTotalBase + nTotalIVA + nTotalRE;
RETURN NEXT rTotales;
end
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1;
CREATE OR REPLACE FUNCTION public."AlbaranBeforeDelete"()
RETURNS trigger AS
$$
begin
DELETE FROM "AlbaMov"
WHERE ("Serie" = OLD."Serie") AND ("NumeroDoc" = OLD."NumeroDoc");
RETURN OLD;
end
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
CREATE OR REPLACE FUNCTION public."AlbaranBeforeUpdate"()
RETURNS trigger AS
$$
begin
NEW."Total" := Round((NEW."BaseImponible" + NEW."IVA" + NEW."RE")::numeric, 2);
RETURN NEW;
end
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
CREATE OR REPLACE FUNCTION public."AlbaMovAfterDelete"()
RETURNS trigger AS
$$
declare nTotalBase "Importes";
declare nTotalIVA "Importes";
declare nTotalRE "Importes";
declare nTotalDtoBase "Importes";
declare nTotalDtoResto "Importes";
declare nTotalDtos "Importes";
declare nTotalLinea "Importes";
declare cCliente "CodigoCliente";
begin
PERFORM "CalculoAlbaranVenta"(OLD."Serie", OLD."NumeroDoc");
nTotalLinea := Round((OLD."TotalUnidades" * OLD."Precio")::numeric, 3);
nTotalDtoBase := Round((nTotalLinea * (OLD."DescuentoBase" / 100))::numeric, 3);
nTotalLinea := nTotalLinea - nTotalDtoBase;
nTotalDtoResto := Round((nTotalLinea * (OLD."DescuentoResto" / 100))::numeric, 3);
nTotalLinea := nTotalLinea - nTotalDtoResto;
nTotalDtos := nTotalDtos + nTotalDtoBase + nTotalDtoResto;
nTotalBase := Round(nTotalLinea::numeric, 2);
nTotalIVA := (nTotalBase * OLD."PorcentajeIVA" / 100);
nTotalRE := (nTotalBase * OLD."PorcentajeRE" / 100);
nTotalIVA := Round(nTotalIVA::numeric, 2);
nTotalRE := Round(nTotalRE::numeric, 2);
nTotalDtos := Round(nTotalDtos::numeric, 2);
PERFORM "SumaArticulo"(OLD."Referencia", OLD."TotalUnidades");
SELECT "Cliente" INTO cCliente FROM "Albaran"
WHERE ("Serie" = OLD."Serie") AND ("NumeroDoc" = OLD."NumeroDoc");
PERFORM "RestaCliente"(cCliente, nTotalBase + nTotalIVA + nTotalRE);
RETURN OLD;
end
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
CREATE TABLE public."Albaran" (
"NumeroDoc" public."NumeroDocumento" NOT NULL,
"Serie" public."Serie" NOT NULL,
"Fecha" date NOT NULL,
"Cliente" public."CodigoCliProv" NOT NULL,
"Nombre" public."RazonSocial",
"BaseImponible" public."Importes",
"IVA" public."Importes",
"RE" public."Importes",
"Notas" public."Memo",
"CodigoDir" public."CodigoDireccion",
"Direccion" public."Direccion",
"Poblacion" public."Poblacion",
"CodigoPostal" public."CodigoPostal",
"Provincia" public."Provincia",
"Pais" public."Pais",
"CIF" public."CIF",
"Total" public."Importes",
"Agente" public."CodigoAgente",
"SuNumeroPedido" public."SuNumeroPedido",
"Telefono" public."Telefono",
"Fax" public."Telefono",
"FormaPago" public."FormaPago",
"Transportista" public."CodigoTransporte",
"Repartidor" public."CodigoRepartidor",
"Portes" public."Importes",
"DebidosPagados" public."Boolean",
"Gastos" public."Importes",
"TotalDescuentos" public."Importes",
"TotalPesoNeto" public."Peso",
"TotalPesoBruto" public."Peso",
"Facturado" public."Boolean",
"Modificado" public."Boolean"
/* Llaves */
CONSTRAINT "PK_Albaran"
PRIMARY KEY ("Serie", "NumeroDoc")
) WITH (
OIDS = FALSE
);
CREATE INDEX "IDX_Albaran_Nombre"
ON public."Albaran"
("Nombre");
CREATE TRIGGER "Albaran_BD"
BEFORE DELETE
ON public."Albaran"
FOR EACH ROW
EXECUTE PROCEDURE public."AlbaranBeforeDelete"();
CREATE TRIGGER "Albaran_BU"
BEFORE UPDATE
ON public."Albaran"
FOR EACH ROW
EXECUTE PROCEDURE public."AlbaranBeforeUpdate"();
CREATE TABLE public."AlbaMov" (
"RecNo" serial NOT NULL,
"Serie" public."Serie" NOT NULL,
"NumeroDoc" public."NumeroDocumento" NOT NULL,
"Referencia" public."CodigoArticulo" NOT NULL,
"Descripcion" public."Descripcion",
"Cantidad" public."Cantidad",
"Precio" public."Importes",
"PrecioCosto" public."Importes",
"PorcentajeIVA" public."Porcentaje",
"PorcentajeRE" public."Porcentaje",
"Almacen" public."CodigoAlmacen",
"Lote" public."Lote",
"Unidades" public."Cantidad",
"TotalUnidades" public."Cantidad",
"CodigoPromocion" public."CodigoArticuloOpcional",
"Promocion" public."Cantidad",
"DescuentoBase" public."Porcentaje",
"DescuentoResto" public."Porcentaje",
"PesoNeto" public."Peso",
"PesoBruto" public."Peso",
"ReferenciaCliente" public."CodigoArticuloOpcional",
"Modificado" public."Boolean",
"FechaCaducidad" date,
"TotalLinea" public."Importes",
"SeriePedido" public."Serie",
"NumeroPedido" public."NumeroDocumento",
/* Llaves */
CONSTRAINT "PK_AlbaMov"
PRIMARY KEY ("RecNo")
) WITH (
OIDS = FALSE
);
CREATE INDEX "IDX_AlbaMov_SerieNumeroDoc"
ON public."AlbaMov"
("Serie", "NumeroDoc", "RecNo");
CREATE TRIGGER "AlbaMov_AD"
AFTER DELETE
ON public."AlbaMov"
FOR EACH ROW
EXECUTE PROCEDURE public."AlbaMovAfterDelete"();在做测试时，我发现如果我从这个函数中删除主表记录，它是不是完美无缺，为什么不被破坏？，我无法理解它。
CREATE OR REPLACE FUNCTION public."Albaran2Factura"
(
IN "cSerieAlbaran" public."SerieDocumento",
IN "nNumeroAlbaran" public."NumeroDocumento"
)
RETURNS SETOF public."SerieNumeroDocumento" AS
$$
declare rDocumento "SerieNumeroDocumento"%ROWTYPE;
declare rMaster RECORD;
declare rDetail RECORD;
declare rConfig RECORD;
declare rIVA RECORD;
declare cRegimenIVA CHAR;
declare nNumeroFactura "NumeroDocumento";
declare nPorcentajeIVAPortes "Importes";
declare nPorcentajeREPortes "Importes";
begin
rDocumento."Serie" := '';
rDocumento."NumeroDoc" := -1;
SELECT * INTO rConfig FROM "Empresa" LIMIT 1;
SELECT "PorcentajeIVA", "PorcentajeRE" INTO rIVA FROM "Iva"
WHERE "Tipo" = rConfig."TipoIVAPortes";
nPorcentajeIVAPortes := rIVA."PorcentajeIVA";
nPorcentajeREPortes := rIVA."PorcentajeRE";
UPDATE "Numera"
SET "NumeroDoc" = "NumeroDoc" + 1
WHERE ("TipoDocumento" = 'FV') AND ("Serie" = "cSerieAlbaran");
SELECT "NumeroDoc" INTO nNumeroFactura FROM "Numera"
WHERE ("TipoDocumento" = 'FV') AND ("Serie" = "cSerieAlbaran");
SELECT * INTO rMaster FROM "Albaran"
WHERE ("Serie" = "cSerieAlbaran") AND ("NumeroDoc" = "nNumeroAlbaran");
SELECT "RegimenIVA" INTO cRegimenIVA FROM "Clientes"
WHERE "Codigo" = rMaster."Cliente";
IF ("cSerieAlbaran" <> 'ZZZ') THEN
IF (cRegimenIVA = 'G') THEN
nPorcentajeREPortes := 0;
ELSIF (cRegimenIVA = 'E') THEN
nPorcentajeIVAPortes := 0;
nPorcentajeREPortes := 0;
END IF; /* IF (cRegimenIVA = 'G') */
ELSE
nPorcentajeIVAPortes := 0;
nPorcentajeREPortes := 0;
END IF; /* IF ("cSerieAlbaran" <> 'ZZZ') */
INSERT INTO "Factura" ("NumeroDoc",
"Serie",
"Fecha",
"Cliente",
"Nombre",
"BaseImponible",
"IVA",
"RE",
"Notas",
"Direccion",
"Poblacion",
"CodigoPostal",
"Provincia",
"CIF",
"Total",
"Agente",
"CodigoDir",
"Pais",
"SuNumeroPedido",
"Telefono",
"Fax",
"FormaPago",
"Transportista",
"Repartidor",
"Portes",
"DebidosPagados",
"Gastos",
"TotalDescuentos",
"TotalPesoNeto",
"TotalPesoBruto",
"PorcentajeIVAPortes",
"PorcentajeREPortes",
"Albaranes",
"Exportada",
"Rapel",
"Cobrada",
"Modificado")
VALUES (nNumeroFactura,
"cSerieAlbaran",
current_date,
rMaster."Cliente",
rMaster."Nombre",
rMaster."BaseImponible",
rMaster."IVA",
rMaster."RE",
rMaster."Notas",
rMaster."Direccion",
rMaster."Poblacion",
rMaster."CodigoPostal",
rMaster."Provincia",
rMaster."CIF",
rMaster."Total",
rMaster."Agente",
rMaster."CodigoDir",
rMaster."Pais",
rMaster."SuNumeroPedido",
rMaster."Telefono",
rMaster."Fax",
rMaster."FormaPago",
rMaster."Transportista",
rMaster."Repartidor",
rMaster."Portes",
rMaster."DebidosPagados",
rMaster."Gastos",
rMaster."TotalDescuentos",
rMaster."TotalPesoNeto",
rMaster."TotalPesoBruto",
nPorcentajeIVAPortes,
nPorcentajeREPortes,
'Albaran ' || "nNumeroAlbaran" || '/' || "cSerieAlbaran",
'0',
'0',
'0',
'1');
FOR rDetail IN SELECT * FROM "AlbaMov"
WHERE ("Serie" = "cSerieAlbaran") AND ("NumeroDoc" = "nNumeroAlbaran")
ORDER BY "RecNo"
LOOP
INSERT INTO "FacMov" ("Serie",
"NumeroDoc",
"Referencia",
"Descripcion",
"Cantidad",
"Precio",
"PorcentajeIVA",
"PorcentajeRE",
"NumeroAlbaran",
"SerieAlbaran",
"FechaAlbaran",
"NumeroPedido",
"SeriePedido",
"PrecioCosto",
"Almacen",
"Lote",
"Unidades",
"TotalUnidades",
"CodigoPromocion",
"Promocion",
"DescuentoBase",
"DescuentoResto",
"PesoNeto",
"PesoBruto",
"ReferenciaCliente",
"Modificado",
"FechaCaducidad",
"NoDescontar",
"Agente",
"Repartidor")
VALUES ("cSerieAlbaran",
nNumeroFactura,
rDetail."Referencia",
rDetail."Descripcion",
rDetail."Cantidad",
rDetail."Precio",
rDetail."PorcentajeIVA",
rDetail."PorcentajeRE",
rMaster."NumeroDoc",
rMaster."Serie",
rMaster."Fecha",
rDetail."NumeroPedido",
rDetail."SeriePedido",
rDetail."PrecioCosto",
rDetail."Almacen",
rDetail."Lote",
rDetail."Unidades",
rDetail."TotalUnidades",
rDetail."CodigoPromocion",
rDetail."Promocion",
rDetail."DescuentoBase",
rDetail."DescuentoResto",
rDetail."PesoNeto",
rDetail."PesoBruto",
rDetail."ReferenciaCliente",
'1',
rDetail."FechaCaducidad",
'0',
rMaster."Agente",
rMaster."Repartidor");
END LOOP;
/********************** Deleting master record work ****************/
DELETE FROM "Albaran"
WHERE ("Serie" = "cSerieAlbaran") AND ("NumeroDoc" = "nNumeroAlbaran");
/**************************************/
rDocumento."Serie" := "cSerieAlbaran";
rDocumento."NumeroDoc" := nNumeroFactura;
RETURN NEXT rDocumento;
end
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;这不起作用：
CREATE OR REPLACE FUNCTION public."BorrarAlbaran"
(
IN "cSerie" public."SerieDocumento",
IN "nNumeroDoc" public."NumeroDocumento"
)
RETURNS void AS
$$
begin
DELETE FROM "Albaran"
WHERE ("Serie" = "cSerie") and ("NumeroDoc" = "nNumeroDoc");
end
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;解决方法：
CREATE OR REPLACE FUNCTION public."BorrarAlbaranVenta"
(
IN "cSerie" public."SerieDocumento",
IN "nNumeroDoc" public."NumeroDocumento"
)
RETURNS void AS
$$
begin
DELETE FROM "AlbaMov"
WHERE ("Serie" = "cSerie") and ("NumeroDoc" = "nNumeroDoc");
DELETE FROM "Albaran"
WHERE ("Serie" = "cSerie") and ("NumeroDoc" = "nNumeroDoc");
end
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
回答 1
Stack Overflow用户
回答已采纳
发布于 2013-05-03 09:34:07
当您删除主表中的记录时，会从详细信息中删除所有记录，我将主表中的字段“”更新为0，但不会删除主表记录。如果我从主表中删除记录，则明细表中没有记录的记录将被顺利删除。
这通常表示触发器是级联的，在before触发器中重新插入行时会产生副作用。
在Postgres中，更新实际上是delete，然后是insert。一旦所有的before触发器都完成了它们的工作，旧的行/ctid就被标记为失效，并创建一个新的行/ctid --这两个操作都是从txid_current()开始的。然后after触发器开始起作用。
这里要理解的一点是，您并不是在操纵行本身。相反，您正在操作给定时间的行的快照，而后者由其ctid和各种元信息引用：
http://www.postgresql.org/docs/9.2/static/ddl-system-columns.html
无论如何，我只是粗略地看了一下，但我猜AlbaranBeforeDelete()是罪魁祸首。
在删除行/ctid1之前，级联删除子表中的行。当您这样做时，row/ctid1仍然被标记为活动的，而不是已经标记为死的……原因也很充分:如果在before delete触发器中返回null，行将不会被删除。
此时，子表的after delete触发器启动并更新row/ctid1。此语句将row/ctid1标记为已死，同时创建新的活动和更新的行/ctid2。
然后，您的初始语句将继续。Postgres将row/ctid1标记为已死(顺便说一句，它已经死了)，after触发器触发。但是，您仍然会留下一个活动的row/ctid2，因为您的原始语句在对每个行/ctid2触发触发器之前计算出受影响的row/ctid2，而您的原始语句并不知道。因此，row/ctid2保留了下来。
修复方法是以这样一种方式更改流程，即在此之前没有任何触发器具有任何副作用。副作用属于after触发器。
诚然，有人可能会说这是Postgres中的一个bug。几年前它咬了我一口，当我这样做的时候，它被认为是一个特性而被忽视了。
顺便说一句，如果上面的情况不是100%清楚的话，这里有另一个正在发生的典型例子：
create table if not exists test (
id serial primary key
);
create table if not exists subtest (
id serial primary key,
test_id int references test(id) on delete cascade
);
create function break_pgsql() returns trigger as $$
begin
return null;
end;
$$ language plpgsql;
create trigger break_pgsql before delete on subtest
for each row
execute procedure break_pgsql();
insert into test default values;
insert into subtest (test_id) select id from test;
delete from test;
select * from test; -- empty
select * from subtest; -- not empty在上面的代码中，Postgres的内置触发器级联删除相关行。生成的delete语句会被发出，但有副作用的before触发器会对其进行干扰，从而产生具有无效外键的行。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/16354088
复制
腾讯云开发者
