Postgres-更新语句作为触发器 [英] Postgres - update statement as a trigger

问题描述

我已经玩了最后一个小时或更长时间，试图将更新语句放入触发器中。我了解UPDATE语句的概念，并且以下代码也可以正常工作

I've been playing around for the last hour or more trying to put an update statement into a trigger. I understand the concept of an UPDATE statement and the below works just fine

UPDATE cars SET country = 'France';

我想要的是将其放入触发器，以便在更新cars表时，该列国家将自动与法国一起更新。

What I want is to put this into a trigger so that when the cars table is updated, the column country will automatically be updated with France.

我一直在尝试调整在网上找到的功能和触发器，但是显然我在声明错误，因为它们要么不执行否则它们会执行，但是在添加新记录时不会更新国家/地区字段。

I've played around with adapting Functions and Triggers that I've found out on the interweb but I'm obviously making the statement wrong as either they don't execute or they execute but don't update the country field when a new record is added.

CREATE FUNCTION update_country () RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        UPDATE cars SET country = 'France' WHERE id = New.id;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql; --The trigger used to update a table.

CREATE TRIGGER update_country_col BEFORE UPDATE ON cars FOR EACH ROW EXECUTE PROCEDURE update_country();

上面的脚本会执行，但不会将France添加到country列。
该函数改编自我在网上发现的一条语句。

The above scripts executes but does not add France to the country column. The function was adapted from a statement that I found out on the web.

Postgres 9.1。

Postgres 9.1.

我知道答案将是如此简单！

I know that the answer is going to be so simple!

推荐答案

在更新触发器中，您应该修改 NEW 记录。
另外，您可能需要从过程中返回 NEW 记录。

In update triggers you should modify NEW record. Also, you may need to return NEW record from procedure.

因此，您应该使用以下命令程序而不是您的程序：

So, you should use following procedure instead of yours:

CREATE FUNCTION update_country () RETURNS TRIGGER AS $$
  BEGIN
    IF (TG_OP = 'UPDATE') THEN
     NEW.country = 'France';
    END IF;
    RETURN NEW; 
  END; $$ LANGUAGE plpgsql;

这篇关于Postgres-更新语句作为触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！