英文:
Why is the column value not persisting after Postgres function triggers after update?
问题
Here's the translated SQL code you provided:
我有一个名为 `Projects` 的表，在执行更新并且记录符合特定条件时，我需要设置某一列的值。该函数触发正确，没有错误，但更新后的列值不会保留。
这是我的 SQL 代码：
create table projects (
id int not null primary key,
status varchar,
number varchar
);
CREATE FUNCTION update_number() RETURNS trigger
LANGUAGE plpgsql
AS
$$
declare
status_code varchar;
begin
SELECT project_codes.code INTO status_code FROM project_codes WHERE project_codes.status = NEW.status;
NEW.number := CONCAT('CA-', status_code, '-', nextval('project_sequence'));
RAISE INFO 'Project: %', NEW;
RETURN NEW;
end
$$;
CREATE TRIGGER update_number_trig
AFTER UPDATE
ON projects
FOR EACH ROW
WHEN (NEW.status = 'CAPITAL')
EXECUTE PROCEDURE update_number();
你正在运行以下命令进行测试：
UPDATE projects SET status = 'CAPITAL' WHERE id = '123456';
函数在更新操作之后执行，我在控制台看到 RAISE 打印：
Project: (123456, CAPITAL, CA-2DC-1)
但是当我在项目表上执行 SELECT 时，number 列是空白的。
为什么设置列值不会保留？
英文:
I have a table called Projects where I need to set a certain column value after an update is executed and the record matches certain conditions. The function triggers correctly and there are no errors but the updated column value does not persist.
Here's my SQL:
create table projects (
id int not null primary key,
status varchar,
number varchar
);
CREATE FUNCTION update_number() RETURNS trigger
LANGUAGE plpgsql
AS
$$
declare
status_code varchar;
begin
SELECT project_codes.code INTO status_code FROM project_codes WHERE project_codes.status = NEW.status;
NEW.number := CONCAT('CA-', status_code, '-', nextval('project_sequence'));
RAISE INFO 'Project: %', NEW;
RETURN NEW;
end
$$;
CREATE TRIGGER update_number_trig
AFTER UPDATE
ON projects
FOR EACH ROW
WHEN (NEW.status = 'CAPITAL')
EXECUTE PROCEDURE update_number();
I'm running the following command to test this:
UPDATE projects SET status = 'CAPITAL' WHERE id = '123456';
The function executes after the update operation, I see in the console that the RAISE prints:
> Project: (123456, CAPITAL, CA-2DC-1)
But when I do a SELECT on the projects table, the number column is blank.
Why is setting the column value not persisting?
答案1
得分: 0
在BEFORE触发器中修改NEW才会产生效果。在AFTER触发器中，行已经被写入，你返回的修改过的NEW最多只会影响在你之后执行的其他AFTER触发器。
你需要使用BEFORE UPDATE触发器。
英文:
Modifying NEW only has an effect in BEFORE triggers. In an AFTER trigger, the row has already been written, and the modified NEW you return will at most affect other AFTER triggers that come after you.
You need to use a BEFORE UPDATE trigger.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论