Postgres触发功能 [英] Postgres trigger function
本文介绍了Postgres触发功能的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要Postgres触发器方面的帮助。
I need help in Postgres triggers.
我的表格有两列:
sold boolean;
id_shop int;
它存储是否出售商品,或商店位于哪个商店。
It stores if item is sold, or at which shop its located at.
我需要一个触发器,如果将卖出更改为true,那么它也会将 id_shop
更改为 NULL
(如果出售则不能在商店中购买……)
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...)
我尝试了不同的方法,但它不起作用或给出更新cmd时出错...
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)
第二,您无需更新表,只需分配将新值添加到NEW行:
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屋!
查看全文