时间戳

首页 > 解决方案 > Postgresql 触发器更新整个列而不是特定行

sql - Postgresql 触发器更新整个列而不是特定行

问题描述

我想要实现的是，当我执行 UPDATE 语句时，我希望 TRIGGER 更新具有insurance_team = 'professional'和expiration_date = current_date的每一行，只需将到期年份设为 +1。

我当前的 TRIGGER 函数虽然更新了整个列。

桌子

CREATE TABLE insurance_premiums (
    contract_code text NOT NULL,
    insurance_team text NOT NULL,
    starting_date date NOT NULL,
    expiration_date date NOT NULL,
    contract_cost float8 NOT NULL,
    vehicle_contract text NOT NULL,
    customer_contract text NOT NULL,
    driver_contract text NOT NULL,
    CONSTRAINT insurance_premiums_pkey PRIMARY KEY (contract_code)
);

扳机

create trigger update_contract after
update
    on
    public.insurance_premiums for each row execute function new_date()

触发功能

CREATE OR REPLACE FUNCTION public.new_date()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
    BEGIN 
        if old.insurance_team = 'professional' then
            if old.expiration_date = CURRENT_DATE then
                new.expiration_date = old.expiration_date + integer '365';
                update insurance_premiums set expiration_date = new.expiration_date;
            end if;
        end if;
        return new;
    END;
$function$
;

更新示例

UPDATE insurance_premiums 
SET contract_cost = 736.33
WHERE contract_code = 'EI-36653';

插入上述示例：

INSERT INTO insurance_premiums (contract_code, insurance_team, starting_date, expiration_date, contract_cost, vehicle_contract, customer_contract, driver_contract) 
VALUES('EI-36653', 'professional', '2019-03-31', '2021-06-24', 12736.3, 'Sprinter', 'Antoine Burgise', 'Prudence Lacer');

旁注：上述更新语句只是测试触发器的示例。

标签： sqlpostgresql

解决方案

根本不要使用 UPDATE。

将触发器定义为before update触发器：

create trigger update_contract 
  BEFORE updateupdate
  on public.insurance_premiums 
  for each row execute function new_date()

那么分配new.expiration_date := ..就足够了：

CREATE OR REPLACE FUNCTION public.new_date()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN 
  if old.insurance_team = 'professional' AND old.expiration_date = CURRENT_DATE then
    new.expiration_date := old.expiration_date + integer '365'; --<<< this is enough
  end if;
  return new;
END;
$function$
;

推荐阅读