Postgres 触发函数 [英] Postgres trigger function

问题描述

我需要 Postgres 触发器方面的帮助.

我的表格有 2 列:

sold boolean;id_shop int;

它存储商品是否售出，或者商品位于哪个商店.

我需要一个触发器，如果​​我把sold"改成true，那么它也会把id_shop改成NULL(如果卖了就不能进店了...)

我尝试了不同的方法，但它不起作用或在更新 cmd 时出现错误...

创建或替换函数 pardota_masina_veikals() RETURNS trigger AS $pardota_masina$开始如果 NEW.sold=true 那么更新 masinasSET id_shop=null WHERE id=NEW.id;万一;退货；结尾;$pardota_masina$ 语言 plpgsql;创建触发器 pardota_masina_nevar_but_veikala在为每一行插入或更新 masinas 后执行程序 pardota_masina_veikals();

解决方案

首先，如果您想更改正在更新(或插入)的行的值，您需要一个 before 触发器

其次，您不需要更新"表，只需将新值分配给新行:

创建或替换函数 pardota_masina_veikals()返回触发器作为$pardota_masina$开始如果 NEW.sold=true 那么NEW.id_shop = NULL;万一;退货；结尾;$pardota_masina$语言 plpgsql;创建触发器 pardota_masina_nevar_but_veikala在插入或更新 masinas 之前对于每一行执行程序 pardota_masina_veikals();

I need help in Postgres triggers.

I have table with 2 columns:

sold boolean;
id_shop int;

It stores if item is sold, or at which shop its located at.

I need a trigger, if I change the "sold" to true, then it also changes the id_shop to NULL (It can't be in shop if sold...)

I tried different ways, but it doesn't work or gives an error on update cmd...

create or replace function pardota_masina_veikals() RETURNS trigger AS $pardota_masina$
begin
  IF NEW.sold=true THEN
    update masinas
      SET id_shop=null WHERE id=NEW.id;
  END IF;
  RETURN NEW;
END;
$pardota_masina$ LANGUAGE plpgsql;


CREATE TRIGGER pardota_masina_nevar_but_veikala 
    AFTER INSERT OR UPDATE ON masinas FOR EACH ROW EXECUTE PROCEDURE pardota_masina_veikals();

解决方案

First of all you need a before trigger if you want to change a value of the row being updated (or inserted)

Secondly you don't need to "update" the table, just assign the new value to the NEW row:

create or replace function pardota_masina_veikals() 
RETURNS trigger 
AS 
$pardota_masina$
begin
  IF NEW.sold=true THEN
    NEW.id_shop = NULL;
 END IF;
RETURN NEW;
END;
$pardota_masina$ 
LANGUAGE plpgsql;

CREATE TRIGGER pardota_masina_nevar_but_veikala 
   BEFORE INSERT OR UPDATE ON masinas 
   FOR EACH ROW EXECUTE PROCEDURE pardota_masina_veikals();

这篇关于Postgres 触发函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！