postgresql Postgres 触发函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10516421/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres trigger function
提问by Martins Svirksts
I need help in Postgres triggers.
我需要 Postgres 触发器方面的帮助。
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_shopto NULL(It can't be in shop if sold...)
我需要一个触发器，如果我将“已售”更改为真，那么它也会更改id_shop为NULL（如果已售出，则不能在商店中......）
I tried different ways, but it doesn't work or gives an error on update cmd...
我尝试了不同的方法，但它不起作用或在更新 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();
回答by a_horse_with_no_name
First of all you need a beforetrigger if you want to change a value of the row being updated (or inserted)
首先，如果要更改正在更新（或插入）的行的值，则需要一个before触发器
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();
