postgresql Postgres 触发器适用于 INSERT，不适用于 DELETE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9897809/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres trigger works for INSERT, not for DELETE
提问by Shea Lutton
I am having a problem with a trigger. I created a trigger and a function to keep track of how many rows are in use by each user in my DB. The INSERT part of the trigger and function work correctly, but the DELETE does nothing. When I insert rows in my app, the rowcount goes up, when I delete, the rowcount does not change.
我的触发器有问题。我创建了一个触发器和一个函数来跟踪我的数据库中每个用户正在使用的行数。触发器和函数的 INSERT 部分工作正常，但 DELETE 什么也不做。当我在我的应用程序中插入行时，行数会增加，当我删除时，行数不会改变。
Here is the TABLE where I keep the row count:
这是我保留行数的表格：
Table "public.rowcount"
Column | Type | Modifiers
------------+---------+-----------
user__id | integer | not null
table_name | text | not null
total_rows | bigint |
Here is my TRIGGER:
这是我的触发器：
CREATE TRIGGER countrows_m_time
AFTER INSERT OR DELETE on m_time
FOR EACH ROW EXECUTE PROCEDURE count_rows_m_time();
And here is the FUNCTION:
这是功能：
CREATE OR REPLACE FUNCTION count_rows_m_time()
RETURNS TRIGGER AS
'
BEGIN
IF TG_OP = ''INSERT'' THEN
UPDATE rowcount
SET total_rows = total_rows + 1
WHERE table_name = TG_RELNAME
AND user__id = (SELECT user__id from vi_m_time_users where m_value_id = NEW.m_value_id);
ELSIF TG_OP = ''DELETE'' THEN
UPDATE rowcount
SET total_rows = total_rows - 1
WHERE table_name = TG_RELNAME
AND user__id = (SELECT user__id from vi_m_time_users where m_value_id = OLD.m_value_id);
END IF;
RETURN NULL;
END;
' LANGUAGE plpgsql;
Any ideas? Many thanks, Shea
有任何想法吗？非常感谢，谢伊
回答by Shea Lutton
After my original post, I went back to simplify the problem. I split the TRIGGER and FUNCTION into separate INSERT and DELETE activities. Both the INSERT TRIGGER and FUNCTION continue to work correctly as an AFTER trigger. So I have excluded it from the post. Here is a simplified problem with the DELETE trigger.
在我原来的帖子之后，我回去简化了这个问题。我将 TRIGGER 和 FUNCTION 拆分为单独的 INSERT 和 DELETE 活动。INSERT TRIGGER 和 FUNCTION 作为 AFTER 触发器继续正常工作。所以我把它从帖子中排除了。这是 DELETE 触发器的一个简化问题。
Here is my new TRIGGER:
这是我的新触发器：
CREATE TRIGGER remrows_m_int
BEFORE DELETE on m_int
FOR EACH ROW EXECUTE PROCEDURE rem_rows_m_int();
And here is my new FUNCTION:
这是我的新功能：
CREATE OR REPLACE FUNCTION rem_rows_m_int()
RETURNS TRIGGER AS
'
BEGIN
IF TG_OP = ''DELETE'' THEN
UPDATE rowcount
SET total_rows = total_rows - 1
WHERE table_name = TG_RELNAME
AND user__id = (SELECT user__id from vi_m_int_users where result_id = OLD.result__id);
END IF;
RETURN OLD;
END;
' LANGUAGE plpgsql;
This trigger is now working if I remove rows on the m_int table. The problem with the AFTER trigger was never resolved, but using a BEFORE with a RETURN OLD seems to be an ok substitute. By hard coding certain variables, the issue was related to the use of the OLD.result__id in the function.
如果我删除 m_int 表上的行，此触发器现在正在工作。AFTER 触发器的问题从未解决，但使用 BEFORE 和 RETURN OLD 似乎是一个不错的替代品。通过对某些变量进行硬编码，问题与函数中 OLD.result__id 的使用有关。
回答by Andrew Lazarus
Returning NULLcancels the INSERT/DELETE operations. (You can use it for referential integrity too complicated to be enforced with a simple constraint.)
返回会NULL取消 INSERT/DELETE 操作。（您可以将它用于过于复杂而无法通过简单约束强制执行的参照完整性。）
You want to return OLDfrom the DELETEand NEWfrom the INSERT.
您想OLD从DELETE和NEW从返回INSERT。
回答by Archmal
CREATE OR REPLACE FUNCTION count_rows_m_time()
RETURNS TRIGGER AS $count_rows_m_time$
DECLARE
BEGIN
IF (TG_OP = 'INSERT') THEN
UPDATE rowcount
SET total_rows = (total_rows + 1)
WHERE table_name = TG_RELNAME
AND user_id = (SELECT user_id FROM vi_m_time_users WHERE m_value_id = NEW.m_value_id);
ELSIF (TG_OP = 'DELETE') THEN
UPDATE rowcount
SET total_rows = (total_rows - 1)
WHERE table_name = TG_RELNAME
AND user_id = (SELECT user_id FROM vi_m_time_users WHERE m_value_id = OLD.m_value_id);
END IF;
RETURN NULL;
END;
$count_rows_m_time$ LANGUAGE plpgsql;
*i think so
*我认同
