#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    1
    Rep Power
    0

    Problem deleting record in master table


    Hello everyone.

    First, sorry for my bad english.

    I am creating a new application in which I use PostgreSQL 9.2.
    I'm trying to use the same "logic" used in Firebird, but apparently does not work on PostgreSQL.

    I have a table Master called "Albaran" and other Detail table called "AlbaMov". I have defined some triggers with corresponding duties which Master table updated when you modify a record in the Detail table. Everything works perfectly except when I want to delete a record in the Master table.

    When you delete a record in the Master table deletes all records from the Detail and I update a field "Total" to 0 in the Master table, but does not delete the Master table record. If I delete the record from the Master table without records in Detail table is removed smoothly.

    I've been testing and have seen that the problem is in the UPDATE to the Master table is done in a function that I call CalculoAlbaranVenta.

    This same system works perfectly in Firebird.

    This function returns a variable of type% ROWTYPE which I use to update a PHP screen.

    Here I leave the definition of the tables with triggers and functions.

    Where can be the problem?

    Greetings and thanks in advance.

    Code:
    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"();
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    12
    It seems like you are trying to solve a schematic problem with a procedural solution.

    Can you show the table schema and an example of how things should be before and then after a delete on the master table?

    If I understand the basic problem correctly, what you need is a schema that describes your rules, not a stack of complicated functions and triggers (setting an attribute to 0 may require a trigger, but I can't tell without seeing the table schema first).

    sql Code:
    CREATE TABLE foo
     (id   SERIAL PRIMARY KEY,
      name text NOT NULL);
     
    CREATE TABLE bar
     (id  SERIAL PRIMARY KEY,
      foo INTEGER REFERENCES foo ON UPDATE CASCADE ON DELETE CASCADE NOT NULL);
    Here foo is the master and bar is the subordinate. Deleting a row in foo will also delete all related rows in bar.

    As for doing other actions, you can create a trigger that updates a column on another table after the delete if you want, of course, but usually there is a way to write your table structure so that all of this is done as a simple consequence of a deletion from foo.

    Data design -- that's what you need to work on, not trigger and function design.

IMN logo majestic logo threadwatch logo seochat tools logo