Postgresql aggregate related values using triggers

Question

I have a database representing a payroll system. Each Payroll is related to several PayrollRows (1 to M relationship), and includes a field summarizing its related payrollRows fields.

Simply said, Payroll includes a field called "amountEurToPay" which is the sum of the "paybackEur" fields of its related PayrollRow records.

I am trying to create a trigger function that automatically completes the amountEurToPay field when a Payroll is created. (indeed it will be created directly with its payrollRow)

I have done this :

-- Generate the trigger function
CREATE OR REPLACE TRIGGER new_payroll_creation  
 AFTER INSERT  
 ON "Payroll"
 FOR EACH ROW  
 EXECUTE PROCEDURE populate_payroll_amountEur();

For the function, I tried this, but without any success.

CREATE OR REPLACE FUNCTION populate_payroll_amounteur()  
  RETURNS TRIGGER   
  LANGUAGE PLPGSQL  
  AS  
$$  
BEGIN  
         UPDATE "Payroll"  
        
         SET "amountEurToPay" =  (   SELECT SUM("paybackEur") 
                                     FROM "PayrollRow"
                                     WHERE "payrollId" = NEW."id")
         WHERE ("id" = NEW."id");  
RETURN NEW;  
END;  
$$

For extra information, my schema looks like this

model PayrollRow {
  id            Int       @id @default(autoincrement())
  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt
  paybackEur    Decimal   @default(0) @db.Decimal(10, 2)
  payroll       Payroll   @relation(fields: [payrollId], references: [id])
  payrollId     Int
}

model Payroll {
  id             Int           @id @default(autoincrement())
  createdAt      DateTime      @default(now())
  updatedAt      DateTime      @updatedAt
  amountEurToPay Decimal       @default(0) @db.Decimal(10, 2)
  payrollRow     PayrollRow[]
}

Could you please provide me some support to achieve that? :)

UPDATE-- It seems that that this works (with a preexisting payroll with id 2) for example, I am a bit confused :

CREATE OR REPLACE FUNCTION populate_payroll_amounteur()  
  RETURNS TRIGGER   
  LANGUAGE PLPGSQL  
  AS  
$$  
BEGIN  
         UPDATE "Payroll"  
        
         SET "amountEurToPay" =  (   SELECT SUM("paybackEur") 
                                     FROM "PayrollRow"
                                     WHERE "payrollId" = 2)
         WHERE ("id" = NEW."id");  
RETURN NEW;  
END;  
$$

---- UPDATE for MiTKo If you request the schema definition , actually I use prisma.io ORM , which simplified for me the creation of the schema. Some reverse engineering would give something like this :

-- CreateTable
CREATE TABLE "Payroll" (
    "id" SERIAL NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,
    "amountEurToPay" DECIMAL(10,2) NOT NULL DEFAULT 0,

    CONSTRAINT "Payroll_pkey" PRIMARY KEY ("id")
);
-- CreateTable
CREATE TABLE "PayrollRow" (
    "id" SERIAL NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,
    "paybackEur" DECIMAL(10,2) NOT NULL DEFAULT 0,
    "payrollId" INTEGER NOT NULL,

    CONSTRAINT "PayrollRow_pkey" PRIMARY KEY ("id")
);

    -- AddForeignKey
    ALTER TABLE "PayrollRow" ADD CONSTRAINT "PayrollRow_payrollId_fkey" FOREIGN KEY ("payrollId") REFERENCES "Payroll"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

user_12041280 · Answer

Try this (it's working for update and delete as well)

        -- Generate the trigger function
    CREATE OR REPLACE TRIGGER new_payroll_creation  
     AFTER INSERT  or UPDATE or delete
     ON "PayrollRow"
     FOR EACH ROW  
     EXECUTE PROCEDURE populate_payroll_amountEur();  
     
    
     
    
CREATE OR REPLACE FUNCTION populate_payroll_amounteur()  
  RETURNS TRIGGER   
  LANGUAGE PLPGSQL  
  AS  
$$  
BEGIN  
     UPDATE "Payroll"         
     SET "amountEurToPay" =  
     ( 
      SELECT COALESCE(SUM("paybackEur") ,0)
      FROM "PayrollRow"
      WHERE "payrollId" = COALESCE( NEW."payrollId",OLD."payrollId")
     )
     WHERE ("id" = (COALESCE( NEW."payrollId",OLD."payrollId")));  
RETURN NEW;  
END;  
$$

Postgresql aggregate related values using triggers

1 Answers