当从另一个表中删除时，如何使 PostgreSQL 在表中插入一行? [英] How to make PostgreSQL insert a row into a table when deleted from another table?

问题描述

我们有一个应用程序，它将根据用户请求从表中删除一行.我无法更改应用程序代码.但是，我想根据要删除的行的信息将一行插入另一个表(有点像日志日志)，其中包含来自其他几个表的信息.

我如何在 PostgreSQL 中实现这一点?

解决方案

编写触发器函数.像这样:

创建或替换函数 trg_backup_row()返回触发 AS$BODY$开始插入其他_tblSELECT (OLD).*, t.other_col -- 旧表中的所有列-- SELECT OLD.col1, OLD.col2, t.other_col -- 替代:来自旧 tbl 的一些 cols来自第三个_tbl tWHERE t.col = OLD.col -- 链接到包含已删除行信息的第三个表AND <unique_condition_to_avoid_multiple_rows_if_needed>;返回空；结尾;$BODY$语言 plpgsql 易失性；

还有一个触发器ON DELETE.像这样:

CREATE TRIGGER delaft删除后上表每行执行程序 trg_backup_row();

关键要素

• 最好让它成为触发器AFTER DELETE 和 FOR EACH ROW.

• 要返回旧表中的所有列，请使用语法 (OLD).*.请参阅有关访问复合类型的手册.或者 OLD.* 也是有效的语法，因为 OLD 被隐式添加到 FROM 子句中.但是，对于 VALUES 表达式，它必须是 (OLD).*.喜欢:

  INSERT INTO other_tbl值((旧).*，some_variable)

• 您可以像我演示的那样包含任何其他表中的值.只需确保获得一行，或者您创建多个条目.

• 当触发器触发AFTER事件时，函数可以RETURN NULL.

<小时>

关于可见性

回应@couling 的谨慎评论.

虽然外键可以声明为DEFERRED，这只会推迟完整性检查，而不是删除本身.由 ON DELETE CASCADE 外键在手头或之前执行的触发器中删除的行将不再可见AFTER DELETE 触发器被调用.(显然，这一切都发生在一个事务中.这些细节对其他事务都不重要，其他事务将看到全部或全部效果.有关 MVCC 模型和事务隔离.)

因此，如果您想在 INSERT 中以这种方式包含来自行的值，请确保在删除这些行之前调用此触发器.>

您可能需要在 BEFORE DELETE 之前触发.

或者这可能意味着您必须相应地对触发器进行排序，很明显，BEFORE 触发器在 AFTER 触发器之前出现.相同级别的触发器按字母顺序执行.

但是，只要我在这里非常精确，我还可以添加对其他 BEFORE 触发器中的行(或依赖行)所做的更改也仅当它们被调用时才可见 之前这个.

我建议将其设为 AFTER 触发器是因为如果其他触发器可能会在执行过程中途取消(回滚)DELETE操作 - 只要以上都不适用.

We have an application, which will delete a row from a table based on user requests. I cannot change the application code. However, I want to insert a row into another table (kinda like a journal log) with information from a few other tables based on information of the row that is being deleted.

How do I achieve this within PostgreSQL?

解决方案

Write a trigger function. Something like this:

CREATE OR REPLACE FUNCTION trg_backup_row()
  RETURNS trigger AS
$BODY$
BEGIN

INSERT INTO other_tbl
SELECT (OLD).*, t.other_col                -- all columns of from old table
-- SELECT OLD.col1, OLD.col2, t.other_col  -- alternative: some cols from old tbl
FROM   third_tbl t
WHERE  t.col = OLD.col  -- link to third table with info from deleted row
AND    <unique_condition_to_avoid_multiple_rows_if_needed>;

RETURN NULL;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

And a trigger ON DELETE. Like this:

CREATE TRIGGER delaft
  AFTER DELETE
  ON tbl
  FOR EACH ROW
  EXECUTE PROCEDURE trg_backup_row();

Key elements

• Best make it a trigger AFTER DELETE and FOR EACH ROW.

• To return all columns from the old table use the syntax (OLD).*. See the manual about accessing composite types. Alternatively OLD.* is valid syntax, too, because OLD is added to the FROM clause implicitly. For a VALUES expression it would have to be (OLD).*, though. Like:

  INSERT INTO other_tbl
  VALUES((OLD).*, some_variable)
  

• You can include values from any other table like I demonstrate. Just make sure to get a single row, or you create multiple entries.

• As the trigger fires AFTER the event, the function can RETURN NULL.

---

About visibility

In response to @couling's watchful comment.

While foreign keys can be declared as DEFERRED, this will only defer the integrity check, not the deletion itself. Rows that are deleted in triggers executed before the one at hand or by ON DELETE CASCADE foreign keys will not be visible any more at the time this AFTER DELETE trigger is called. (It all happens in one transaction obviously. None of these details matter for other transactions, which will see all or none of the effects. Refer to the manual for more about the MVCC model and transaction isolation.)

Therefore, if you want to include values from rows depending in such a way in your INSERT, be sure to call this trigger before those rows get deleted.

You may have to you make this trigger BEFORE DELETE.

Or it can mean that you have to order your triggers accordingly, BEFORE triggers come before AFTER triggers, obviously. And triggers at the same level are executed in alphabetical order.

However, as long as I am super precise here, I might also add that changes made to the row (or depending rows) in other BEFORE triggers are also only visible if those are called before this one.

My advice to make it an AFTER trigger was because it is less prone to complications and cheaper if other trigger might cancel (roll back) the DELETE half way through the operation - as long as none of the above applies.

这篇关于当从另一个表中删除时，如何使 PostgreSQL 在表中插入一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！