问题描述
我们有一个应用程序,它将根据用户请求从表中删除一行.我无法更改应用程序代码.但是,我想根据要删除的行的信息,将来自其他几个表的信息插入到另一个表中(类似于日志日志).
如何在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
外键执行的触发器中删除的行将不再可见. (所有事务显然都发生在一个事务中.所有这些细节都与其他事务无关,这将看不到全部或全部影响.有关 MVCC模型和事务隔离.)
因此,如果要在INSERT
中包括这样的行中的值,请确保在删除这些行之前之前调用此触发器.
您可能必须使此触发器成为BEFORE DELETE
.
或者这可能意味着您必须相应地对触发器进行排序,显然,BEFORE
触发器要先于AFTER
触发器.并且同一级别的触发器按字母顺序执行. /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
andFOR EACH ROW
.To return all columns from the old table use the syntax
(OLD).*
. See the manual about accessing composite types. AlternativelyOLD.*
is valid syntax, too, becauseOLD
is added to theFROM
clause implicitly. For aVALUES
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 canRETURN 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在表中插入行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!