PostgreSQL 触发器使默认值参考另一个表

Question

PostgreSQL 触发器使默认值参考另一个表

3

PostgreSQL默认值不能包含变量或引用表中的任何其他列，也不能引用不同表中的列。

然而，可以使用触发器创建一个“默认值”，以以下方式运行。首先，让我举两个示例表说明：

create table projects
(
id serial primary key,
created_at timestamp with time zone default now()
);

create table last_updated
(
project_id integer primary key references projects,
updated_at timestamp with time zone default ...
);

在第二个表格（last_updated）中，我希望默认值类似于 default projects(created_at)。也就是说，如果未指定 updated_at 的日期，则查看项目表中引用的 project_id，找到创建日期 created_at 并将 updated_at 设置为此日期。但是，你不能按照我问题中第一段所述进行编写。

那么，如何编写触发器以实现此功能呢？

- Alex

1

你可以创建一个插入触发器来检查 NEW.updated_at 是否为 NULL，如果是，则从 projects 中选择适当的值并将其赋给 NEW.updated_at。 - Politank-Z

请注意，这样的触发器的唯一目的是维护冗余。从last_updated.project_id -> projects.created_at的引用将产生相同的值；即使在以后的阶段也是如此。 - wildplasser

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Patrick · Accepted Answer

正确的答案取决于您未指定的内容。通常，人们会对“projects”表进行更新，然后在“last_updated”表中审计，使用“projects”表上的“AFTER UPDATE”触发器：

CREATE FUNCTION audit_project_update () RETURNS trigger AS $$
BEGIN
  INSERT INTO last_updated VALUES
    (NEW.id,   -- NEW refers to the updated record in the projects table
     now()     -- this would be the logical value, but can use NEW.created_at
     -- other columns, possibly log session_user
  );
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER tr_projects_update
  AFTER UPDATE ON projects
  FOR EACH ROW EXECUTE PROCEDURE audit_project_update();

请注意，在这种方法中，永远不会出现在表last_updated上进行INSERT操作而没有为updated_at指定值的情况，假设您不会将GRANT INSERT授予任何角色在表last_updated上，因为触发器函数总是指定now()。在表定义中，您不再需要指定默认值：触发器为您提供了所需的自动化行为。

您所述的问题 - 并在下面的评论中确认 - 也将使用触发器，但是在last_updated表上：

CREATE FUNCTION project_last_updated () RETURNS trigger AS $$
BEGIN
  IF (NEW.updated_at IS NULL) THEN
    SELECT created_at INTO NEW.updated_at
    FROM projects
    WHERE id = NEW.project_id;
  END IF;
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER tr_projects_update
  BEFORE INSERT ON last_updated
  FOR EACH ROW EXECUTE PROCEDURE project_last_updated();

这个规范引出了一个问题，为什么不直接在“projects”表中添加一个“updated_at”列呢？由于“last_update”表中的“project_id”列是主键，因此每个项目只能存储一个最后更新日期。请注意保留HTML标签。