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

问题描述

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

如何在PostgreSQL中实现这一目标?

解决方案

编写触发函数.像这样:

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;

和触发器ON DELETE.像这样:

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

关键要素

• 最好使其成为触发AFTER DELETE和 .

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

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

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

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

---

关于可见度

响应@couling的谨慎评论.

虽然外键可以声明为DEFERRED ，这只会推迟完整性检查，而不是删除本身.在调用AFTER DELETE触发器时，在或之前通过ON DELETE CASCADE外键执行的触发器中删除的行将不再可见. (所有事务显然都发生在一个事务中.所有这些细节都与其他事务无关，这将看不到全部或全部影响.有关字母顺序执行. /p>

但是，只要我在这里非常精确，我还可能要补充一点，即在其他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屋！