使用触发器的PostgreSQL聚合相关值

Question

我有一个代表薪资系统的数据库。 每个薪资与几个 payrollrows （对M关系为1）有关，并包括一个字段，汇总了其相关的Payrollrows字段。

简而言之，付款包括一个名为“金额的”字段，该字段是其相关 payrollrow 记录的“ paybackeur”字段的总和。

我正在尝试创建一个触发函数，该功能会在创建工资单时自动完成金额的字段。 （的确，它将直接使用其Payrollrow创建）

我已经做到了：

-- Generate the trigger function
CREATE OR REPLACE TRIGGER new_payroll_creation  
 AFTER INSERT  
 ON "Payroll"
 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 SUM("paybackEur") 
                                     FROM "PayrollRow"
                                     WHERE "payrollId" = NEW."id")
         WHERE ("id" = NEW."id");  
RETURN NEW;  
END;  
$$

有关额外的信息，我的模式看起来像是

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[]
}

您可以为我提供一些支持以实现这一目标吗？ ：）

更新 - 例如，这起作用（具有ID 2的薪水2），例如，我有点困惑：

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;  
$$

----更新Mitko 如果您请求架构定义，则实际上我会使用Prisma.io Orm，这为我简化了模式的创建。 一些逆向工程会提供这样的东西：

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

Answer 1

尝试一下（它正在用于更新和删除）

        -- 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;  
$